Complicated Formula

[Bu77al0]

I am simply trying to associate the value in a cell with a range of numbers in the following formulas. Based on the range the cell value falls into, the result will be a number from, for example, 1 to 5. Neither of the following two formulas work. Any suggestions on how to get either one to work or should I be using a different one:

=IF(C26>-0.005,0,IF(C26>-0.02,-1,IF(C26>-0.04,-2,IF(C26<-0.041,-3,IF(C26<0.005,0,IF(C26<0.02,1,IF(C26<0.04,2,IF(C26>0.041,3,""))))))))

=INDEX({0;1;2;3;4},MATCH(D26,{0;0.005},{0.0051;0.019},{.02;.029},{.03;.049},{.05;.15}))

Thank you.

 

[saon]

hi,
your if formula has some mistakes. if you write what you want to do clearly, may i arrange the formula correctly, and also i could not understand what you want to do with your index formula!

so, if you get clear there might be an answer.

 

[maxflia10]

Try using VLOOKUP

 

[Bu77al0]

My apologies:

Let's say a certain cell (in this case C26) has a value of 0.51%. Based on that value, I want to "award" that cell a score of, for example, 1 point. I want to following formula to be able to find that number (0.51%)within the range of possibilites in the formula.In this case, it should fall within the 6th IF function. I.e., 0.51% is less than 0.02 or 2.0%.

=IF(C26>-0.005,0,IF(C26>-0.02,-1,IF(C26>-0.04,-2,IF(C26<-0.041,-3,IF(C26<0.005,0,IF(C26<0.02,1,IF(C26<0.04,2,IF(C26>0.041,3,""))))))))

Please let me know if you need further explanation.

Thank you.

 

[Bu77al0]

Please tell me where to put VLOOKUP in the formula.

Thank you.

 

[attc]

you could try something like:

=VLOOKUP(A1,{0,1;0.005,2;0.02,3;0.03,4;0.05,5;0.15,0},2)

it's probably easier to put your ranges in an Mx2 area of your spreadsheet to do the vlookup, eg:

Book1
	A	B	C	D
1	0.03	4	4
2
3		0	1
4		0.005	2
5		0.02	3
6		0.03	4
7		0.05	5
8		0.15	0
Sheet1

ps1: sorry, missed MaxFlia's and the other posts
ps2: don't know if I got the values right for what you want, but you can modify as necessary
This message was edited by attc on 2002-11-07 10:37

 

[Bu77al0]

Thank you for the vlookup formula. I hate to sound stupid, but I also need to cover the same range of numbers on the negative side. I.e., 0 to -.50%, -.51 to -1.9%, -2.0% to -2.9%, -3.0% to -4.9% and -5.0% to -15.0%. The results should also go from -1 to -5.

Thank you.

 

[maxflia10]

A small example of VLOOKUP, modify to suit your needs. A1:B6 should be "named", C1 is the lookup value, in your case C26, 2 is the column to get the values and 0 is looking for an exact match

Book1
	A	B	C	D
1	-5	1	-3	3
2	-4	2
3	-3	3
4	1	4
5	2	5
6	3	6
Sheet1

 

[Aladin Akyurek]

On 2002-11-07 10:18, Bu77al0 wrote:
I am simply trying to associate the value in a cell with a range of numbers in the following formulas. Based on the range the cell value falls into, the result will be a number from, for example, 1 to 5. Neither of the following two formulas work. Any suggestions on how to get either one to work or should I be using a different one:

=IF(C26>-0.005,0,IF(C26>-0.02,-1,IF(C26>-0.04,-2,IF(C26<-0.041,-3,IF(C26<0.005,0,IF(C26<0.02,1,IF(C26<0.04,2,IF(C26>0.041,3,""))))))))

=INDEX({0;1;2;3;4},MATCH(D26,{0;0.005},{0.0051;0.019},{.02;.029},{.03;.049},{.05;.15}))

Thank you.

=IF(ISNUMBER(C26),IF(C26>=-0.005,INDEX({0;-1;-2;-3;0;1;2;3},MATCH(C26,{-0.005;-0.02;-0.04;-0.041;0.005;0.02;0.04;0.041})),""),"")

Or, construct a table which is easier maintain...

Book7
	A	B	C	D
1
2	-0.005	0
3	-0.02	-1
4	-0.04	-2
5	-0.041	-3
6	0.005	0
7	0.02	1
8	0.04	2
9	0.041	3
10
11	Lightgreenarea
12	isnamedasTable.
13
Sheet1

and use, as is proposed...

=IF(ISNUMBER(C26),IF(C26>=-0.005,VLOOKUP(C26,Table,2),""),"")

Check for the correctness of the associations between percentages and numbers.