Complicated Formula

Bu77al0

New Member
Joined
Nov 5, 2002
Messages
9
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

saon

New Member
Joined
Aug 7, 2002
Messages
16
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.
 

Bu77al0

New Member
Joined
Nov 5, 2002
Messages
9
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

New Member
Joined
Nov 5, 2002
Messages
9

ADVERTISEMENT

Please tell me where to put VLOOKUP in the formula.

Thank you.
 

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
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
ABCD
10.0344
2
301
40.0052
50.023
60.034
70.055
80.150
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

New Member
Joined
Nov 5, 2002
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
May 20, 2002
Messages
890
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
ABCD
1-51-33
2-42
3-33
414
525
636
Sheet1
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
ABCD
1
2-0.0050
3-0.02-1
4-0.04-2
5-0.041-3
60.0050
70.021
80.042
90.0413
10
11Lightgreenarea
12isnamedasTable.
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top