IF question - i think?!

marymargaret · May 24th, 2002, 02:39 PM

i have 5 cells...1st cell value determines value of 5th cell. IF cell B1 = 24, i would like E1 to display .70. IF B1=25, E1 would be .75. IF cell B1=26, E1 would be .80. Is there some way for me to do this? i was only able to put one IF formula in at a time. Any help greatly appreciated. Thanks.

sen_edp · May 24th, 2002, 03:03 PM

hello MaryMargaret

You can use the vlookup function to do this.
take a look at the example below

Microsoft Excel - PIVOT TABLE EXAMPLE.xls

___Running: xl2000 : OS = Windows (32-bit) NT 5.00

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

I12

=

A

B

C

D

E

F

1

25

:alert('=VLOOKUP(B1,D5:E11,2,FALSE)')>75

2

3

4

5

24

70

6

25

75

7

26

80

8

27

85

9

28

90

10

29

95

11

30

100

12

Sheet2

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.28]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

you can expand the list if you want with more values

and another way of doing it herebelow

Microsoft Excel - PIVOT TABLE EXAMPLE.xls

___Running: xl2000 : OS = Windows (32-bit) NT 5.00

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

D7

=

A

B

C

D

E

F

1

25

binary logic way

:alert('=((B1=24)*70)+((B1=25)*75)+((B1=26)*80)+((B1=27)*85)+((B1=28)*90)+((B1=29)*95)+((B1=30)*100)')>75

2

3

4

5

6

Sheet2

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.28]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

_________________
Best Regards,
Andreas
using Excel 2000 - Windows 2000 prof.

[ This Message was edited by: sen_edp on 2002-05-24 14:16 ]

Aladin Akyurek · May 24th, 2002, 03:12 PM

Quote:

On 2002-05-24 13:39, marymargaret wrote:
i have 5 cells...1st cell value determines value of 5th cell. IF cell B1 = 24, i would like E1 to display .70. IF B1=25, E1 would be .75. IF cell B1=26, E1 would be .80. Is there some way for me to do this? i was only able to put one IF formula in at a time. Any help greatly appreciated. Thanks.

A bit fancy but efficient:

=(B1>=24)*0.7+(B1>=24)*(B1-24)*0.05

One thing that is missing is what is upper limit for B1 or E1.

marymargaret · May 24th, 2002, 03:17 PM

Thank you so much for your reply. the vlookup is going to take some studying for me. would it be possible for you to make hyper links for a couple more numbers in the E column so i can try to figure this one out? my manual isn't very thorough. in fact it doesn't even talk about vlookup. aghhh! thanks again.

Yogi Anand · May 24th, 2002, 03:21 PM

Andreas' solution is very comprehensive, and will cover all the bases for you.
However in response to you question whether an IF function can be used ...

=IF(B1=24,0.7,IF(B1=25,0.75,IF(B1=26,0.8,"somethingelse")))

Regards!

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 12:55 ]

Aladin Akyurek · May 24th, 2002, 03:32 PM

There are 2 reasonable routes: The VLOOKUP or the "binary" route I suggested. I'd avoid any longish IF route even if recommended or mentioned by the doctor.

[ This Message was edited by: Aladin Akyurek on 2002-05-24 14:34 ]

sen_edp · May 24th, 2002, 03:34 PM

Hello Yogi

The bad thing about nested if statements is the limit of seven , otherwise they work pretty good.

Yogi Anand · May 24th, 2002, 03:43 PM

Hi Andreas and Aladin:
I fully agree with the statements from both of you. I qualified my response with the statement that marymargaret sort of inquired in the original post whether the IF function can be used.

Regards!

Edit: corrected spelling mistake for Andreas

[ This Message was edited by: Yogi Anand on 2002-05-24 19:44 ]

marymargaret · May 24th, 2002, 04:48 PM

hello all, many thanks for the wonderful info. i actually was very lucky in the fact that my range was 24-30 (7 fields! yeah!) so the IF condition does the trick for me. i am so thankful, because the vlookup was looking pretty intimidating. thank you all.

Mark W. · May 27th, 2002, 02:34 PM

=TREND({0.7,0.75},{24,25},B1)

May 24th, 2002, 03:34 PM	#7
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	Hello Yogi The bad thing about nested if statements is the limit of seven , otherwise they work pretty good. __________________ Best Regards Andreas

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 24th, 2002, 02:39 PM	#1
marymargaret New Member Join Date: May 2002 Posts: 12	i have 5 cells...1st cell value determines value of 5th cell. IF cell B1 = 24, i would like E1 to display .70. IF B1=25, E1 would be .75. IF cell B1=26, E1 would be .80. Is there some way for me to do this? i was only able to put one IF formula in at a time. Any help greatly appreciated. Thanks.

May 24th, 2002, 03:17 PM	#4
marymargaret New Member Join Date: May 2002 Posts: 12	Thank you so much for your reply. the vlookup is going to take some studying for me. would it be possible for you to make hyper links for a couple more numbers in the E column so i can try to figure this one out? my manual isn't very thorough. in fact it doesn't even talk about vlookup. aghhh! thanks again.

May 24th, 2002, 03:21 PM	#5
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Andreas' solution is very comprehensive, and will cover all the bases for you. However in response to you question whether an IF function can be used ... =IF(B1=24,0.7,IF(B1=25,0.75,IF(B1=26,0.8,"somethingelse"))) Regards! _________________ Yogi Anand Edit: Deleted inactive website from hardcoded signature [ This Message was edited by: Yogi Anand on 2003-01-19 12:55 ]

May 24th, 2002, 03:32 PM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	There are 2 reasonable routes: The VLOOKUP or the "binary" route I suggested. I'd avoid any longish IF route even if recommended or mentioned by the doctor. [ This Message was edited by: Aladin Akyurek on 2002-05-24 14:34 ]

May 24th, 2002, 03:43 PM	#8
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Andreas and Aladin: I fully agree with the statements from both of you. I qualified my response with the statement that marymargaret sort of inquired in the original post whether the IF function can be used. Regards! Edit: corrected spelling mistake for Andreas [ This Message was edited by: Yogi Anand on 2002-05-24 19:44 ]

May 24th, 2002, 04:48 PM	#9
marymargaret New Member Join Date: May 2002 Posts: 12	hello all, many thanks for the wonderful info. i actually was very lucky in the fact that my range was 24-30 (7 fields! yeah!) so the IF condition does the trick for me. i am so thankful, because the vlookup was looking pretty intimidating. thank you all.

May 27th, 2002, 02:34 PM	#10
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=TREND({0.7,0.75},{24,25},B1)