Formula Help IF?

[~Natasha~]

I am in need of some help.

I tried this: =IF(A1=0,"0",IF(A1=1,"0",IF(A1=15,"1",""))) I only kno
basics so I am running up into a problem once I pass 7 nested items.


Any help would be greatly appreciated.

any number between 00-07 = 0
any number between 08-22 = 1
any number between 23-37 = 2
any number between 38-52 = 3
any number between 53-67 = 4
any number between 68-82 = 5
any number between 83-97 = 6
any number between 98-112 = 7
any number between 113-127 = 8
any number between 128-142 =

 

[Frank Kabel]

Hi
1. Create a separate table on a separate sheet which acts
as lookup table:
A B
1 0 0
2 8 1
3 23 2
....

Now use the following formula:
=VLOOKUP(A1,'lookup_sheet'!A1:B100,2,1)

 

[Govind]

Hi,

Arrange your criteria like this .

column C column D

0 0
8 1
23 2
38 3

and so on.....

Then lets say you have your number in cell A1, use this formula in cell B1

=VLOOKUP(A1,C1100,2,TRUE)

Regards

Govind.

 

[Arvi Laanemets]

Hi

=IF(AND(A1>=0,A1<=142),(A1>7)+(A1>22)+(A1>37)+(A1>52)+(A1>67)+(A1>82)+(A1>97
)+(A1>112)+(A1>127)+(A1>142),"")

 

[Ron Rosenfeld]

I am in need of some help.

I tried this: =IF(A1=0,"0",IF(A1=1,"0",IF(A1=15,"1",""))) I only know
basics so I am running up into a problem once I pass 7 nested items.


Any help would be greatly appreciated.

any number between 00-07 = 0
any number between 08-22 = 1
any number between 23-37 = 2
any number between 38-52 = 3
any number between 53-67 = 4
any number between 68-82 = 5
any number between 83-97 = 6
any number between 98-112 = 7
any number between 113-127 = 8
any number between 128-142 = 9

Assuming your numbers are integers since you have left undefined what you want
for a number like 7.5, then, although I'm sure there's a mathematical
relationship you could use, a lookup function is easiest to implement:

=IF(AND(A1>=0,A1<=142),HLOOKUP(A1,
{0,8,23,38,53,68,83,98,113,128;0,1,2,3,4,5,6,7,8,9},2),
"out of range")

The array constants in the above formula can be replaced by ranges, with the
numbers in the ranges.


--ron

 

[Jack Schitt]

=(A1>127)+(A1>112)+(A1>97)+(A1>82)+(A1>67)+(A1>52)+(A1>37)+(A1>22)+(A1>7)

HTH

 

[Jack Schitt]

Oops, I see Arvi posted more or less my solution. But mine has error,
should be


=(A1>127)+(A1>112)+(A1>97)+(A1>82)+(A1>67)+(A1>52)+(A1>37)+(A1>22)+(A1>7)-9*
(A1>142)

=(A1>127)+(A1>112)+(A1>97)+(A1>82)+(A1>67)+(A1>52)+(A1>37)+(A1>22)+(A1>7)

HTH

 

[Dana DeLouis]

Would this work for you?

=INT((7+A1)/15)

HTH
Dana DeLouis