A Fun Formula

  • Thread starter Thread starter natei6
  • Start date Start date
N

natei6

Hi to all,

I need a formula that says: If A1 is a number ending with 1,2,6 or
then 2*9.125, If A1 is a number that ends with 3 or 8, then 3*9.125
if A1 is a number ending with 4 or 9 then 4*9.125, If A1 is a numbe
that ends with 0 or 5, then "".

Thankyou Much!

Nathan Sargean
 
If you can live with a zero, try this:

=2.195*((OR(--RIGHT(A2)={1,2,6,7})*2)+(OR(--RIGHT(A2)={3,8})*3)+(OR(--RIGHT(
A2)={4,9})*4))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi to all,

I need a formula that says: If A1 is a number ending with 1,2,6 or 7
then 2*9.125, If A1 is a number that ends with 3 or 8, then 3*9.125,
if A1 is a number ending with 4 or 9 then 4*9.125, If A1 is a number
that ends with 0 or 5, then "".

Thankyou Much!

Nathan Sargeant
 
Hi Nathan..........

You could create a VLOOKUP table (named AnswerTable) with your 0-9
rightmost digits and their corresponding values to return, then in B1 put:

=VLOOKUP(RIGHT(A1)*1,AnswerTable,2,FALSE)

Vaya con Dios,
Chuck, CABGx3
 
CLR said:
*Hi Nathan..........

You could create a VLOOKUP table (named AnswerTable) with your 0-9
rightmost digits and their corresponding values to return, then in B
put:

=VLOOKUP(RIGHT(A1)*1,AnswerTable,2,FALSE)

Vaya con Dios,
Chuck, CABGx3
Click to expand...

Hi

Thanks for the quick response and suggestion, but if possible I woul
like to avoid the vlookup solution for what I'm doing, is there anothe
way?

Thanks in advance,

Nathan Sargean
 
I need a formula that says: If A1 is a number ending with 1,2,6 or 7
then 2*9.125, If A1 is a number that ends with 3 or 8, then 3*9.125,
if A1 is a number ending with 4 or 9 then 4*9.125, If A1 is a number
that ends with 0 or 5, then "".
Click to expand...
...

What *precisely* do you mean by 'number'? Text that looks like a number? A
constant whole number? A constant number possibly including a fractional part?
The result of a formula returning a number that could be either a whole number
or a number including a fractional part? It's the possibility of handling
fractions that's the tricky bit, so I'll assume text or whole numbers.

without error checking
=IF(MOD(-RIGHT(A1,1),5),MAX(2,MOD(-RIGHT(A1,1),5))*9.125,"")

with error checking
=IF(ISNUMBER(-RIGHT(A1,1)),IF(MOD(-RIGHT(A1,1),5),
MAX(2,MOD(-RIGHT(A1,1),5))*9.125,""),"INVALID")
 
Harlan said:
*"natei6 <" wrote...
...

What *precisely* do you mean by 'number'? Text that looks like
number? A
constant whole number? A constant number possibly including
fractional part?
The result of a formula returning a number that could be either
whole number
or a number including a fractional part? It's the possibility o
handling
fractions that's the tricky bit, so I'll assume text or whol
numbers.

without error checking
=IF(MOD(-RIGHT(A1,1),5),MAX(2,MOD(-RIGHT(A1,1),5))*9.125,"")

with error checking
=IF(ISNUMBER(-RIGHT(A1,1)),IF(MOD(-RIGHT(A1,1),5),
MAX(2,MOD(-RIGHT(A1,1),5))*9.125,""),"INVALID")
Click to expand...

Hi Harlan,

Thanks, I mean a constant whole number. The formula you gave me yield
the opposite results I need. For example if I enter 1 or 2 into A1
should get 18.25, instead I'm getting 36.5 or 27.375 respectivly.

How do I reverse the formula?

Thanks Again,

Nathan Sargean
 
Harlan Grove wrote: ... ...
Thanks, I mean a constant whole number. The formula you gave me yields
the opposite results I need. For example if I enter 1 or 2 into A1 I
should get 18.25, instead I'm getting 36.5 or 27.375 respectivly.

How do I reverse the formula?
Click to expand...

I screwed up. Remove the minus signs from the MOD calls. So

without error checking
=IF(MOD(RIGHT(A1,1),5),MAX(2,MOD(RIGHT(A1,1),5))*9.125,"")

with error checking
=IF(ISNUMBER(-RIGHT(A1,1)),IF(MOD(RIGHT(A1,1),5),
MAX(2,MOD(RIGHT(A1,1),5))*9.125,""),"INVALID")
 
Here's just another idea without error checking:

=MOD(332,4+MOD(INT(MOD(A1,10)),5))*9.125

Dana DeLouis
 
Dana said:
*Here's just another idea without error checking:

=MOD(332,4+MOD(INT(MOD(A1,10)),5))*9.125

Dana DeLouis


Harlan Grove said:
"natei6 <" wrote...
Click to expand...

Thank you Harlan and Dana.

YOU have been a great blessing. This formula is working beautifully!

All the best,

Nathan Sargean
Click to expand...
 
Back
Top