IF calculation problem

JRRT · Mar 20th, 2002, 09:51 AM

I am new to excel and am trying to us the IF function to do a calculation.
The calculation will be done in D3 What I want to do is if the letter "I" is typed in B3 it should divide the value that is entered in C3 by 2.5. Or if the letter "D" is typed in B3 it should divide the value that is entered in C3 by 1.5. Is anything like this possible?

Audiojoe · Mar 20th, 2002, 09:54 AM

Sure, the formula that goes in D3 should say:

=if(b3="i",c3/2.5,if(b3="d",c3/1.5,0))

HTH

spg2112 · Mar 20th, 2002, 09:59 AM

I am new to excel and am trying to us the IF function to do a calculation.
The calculation will be done in D3 What I want to do is if the letter "I" is typed in B3 it should divide the value that is entered in C3 by 2.5. Or if the letter "D" is typed in B3 it should divide the value that is entered in C3 by 1.5. Is anything like this possible?

try =IF(B3="I",C3/2.5,IF(B3="D",C3/1.5,))

JRRT · Mar 20th, 2002, 09:59 AM

Thanks works great!!

Dave Patton · Mar 20th, 2002, 10:03 AM

Do calculations but if criteria not present
put blank into the cell ("").

=IF(B3="I",C3/2.5,IF(B3="D",C3/1.5,""))

Do calculations but if criteria is not True,
put 0 in the cell.
Use either of the following.

=IF(B3="I",C3/2.5,IF(B3="D",C3/1.5,0))

=(B3="I")*C3/2.5+(B3="D")*C3/1.5

JRRT · Mar 20th, 2002, 10:52 AM

This is a different question than the previous but I typed the following in cell C21 and it says "#VALUE!"
=IF(E3:E17="Pioneer 33r87",B21-D3:D17)
What I would like to do is if the text "pioneer 33r87" is found in cells E3 through E17 then take the number entered in the cell that is to the left of it i.e. D3 and subtract that amount from cell B21. What would the correct formula look like. Thanks.

Dave Patton · Mar 20th, 2002, 11:16 AM

=B21-SUMPRODUCT((E3:E17="Pioneer 33r87")*(D3:D17))

or use cell reference for criteria
=B21-SUMPRODUCT((E3:E17=E3)*(D3:D17))

JRRT · Mar 20th, 2002, 11:19 AM

You guys are awesome! Again it works great.

JRRT · Mar 20th, 2002, 11:44 AM

After looking at this spreadsheet more I keep finding ways to make it better. For example the name in cell A21 is what is to be looked for in E3:E17. How can I get the below formula to changed the E3:e17 search when a new name is typed in A21 and still perform the same calculation? Thanks

=B21-SUMPRODUCT((E3:E17="Pioneer 33r87")*(D3:D17))

Dave Patton · Mar 20th, 2002, 12:12 PM

Please read above suggestion

or use cell reference for criteria
=B21-SUMPRODUCT((E3:E17=E3)*(D3:D17))

Change the E3 to cell that contains your criteria!

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

Mar 20th, 2002, 09:51 AM	#1
JRRT New Member Join Date: Mar 2002 Posts: 21	I am new to excel and am trying to us the IF function to do a calculation. The calculation will be done in D3 What I want to do is if the letter "I" is typed in B3 it should divide the value that is entered in C3 by 2.5. Or if the letter "D" is typed in B3 it should divide the value that is entered in C3 by 1.5. Is anything like this possible?

Mar 20th, 2002, 09:54 AM	#2
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	Sure, the formula that goes in D3 should say: =if(b3="i",c3/2.5,if(b3="d",c3/1.5,0)) HTH

Mar 20th, 2002, 09:59 AM	#3
spg2112 Board Regular Join Date: Feb 2002 Location: Dallas, TX Posts: 312	I am new to excel and am trying to us the IF function to do a calculation. The calculation will be done in D3 What I want to do is if the letter "I" is typed in B3 it should divide the value that is entered in C3 by 2.5. Or if the letter "D" is typed in B3 it should divide the value that is entered in C3 by 1.5. Is anything like this possible? try =IF(B3="I",C3/2.5,IF(B3="D",C3/1.5,))

Mar 20th, 2002, 10:03 AM	#5
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Do calculations but if criteria not present put blank into the cell (""). =IF(B3="I",C3/2.5,IF(B3="D",C3/1.5,"")) Do calculations but if criteria is not True, put 0 in the cell. Use either of the following. =IF(B3="I",C3/2.5,IF(B3="D",C3/1.5,0)) =(B3="I")C3/2.5+(B3="D")C3/1.5

Mar 20th, 2002, 10:52 AM	#6
JRRT New Member Join Date: Mar 2002 Posts: 21	This is a different question than the previous but I typed the following in cell C21 and it says "#VALUE!" =IF(E3:E17="Pioneer 33r87",B21-D3:D17) What I would like to do is if the text "pioneer 33r87" is found in cells E3 through E17 then take the number entered in the cell that is to the left of it i.e. D3 and subtract that amount from cell B21. What would the correct formula look like. Thanks.

Mar 20th, 2002, 11:16 AM	#7
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	=B21-SUMPRODUCT((E3:E17="Pioneer 33r87")(D3:D17)) or use cell reference for criteria =B21-SUMPRODUCT((E3:E17=E3)(D3:D17))

Mar 20th, 2002, 11:19 AM	#8
JRRT New Member Join Date: Mar 2002 Posts: 21	You guys are awesome! Again it works great.

Mar 20th, 2002, 11:44 AM	#9
JRRT New Member Join Date: Mar 2002 Posts: 21	After looking at this spreadsheet more I keep finding ways to make it better. For example the name in cell A21 is what is to be looked for in E3:E17. How can I get the below formula to changed the E3:e17 search when a new name is typed in A21 and still perform the same calculation? Thanks =B21-SUMPRODUCT((E3:E17="Pioneer 33r87")*(D3:D17))

Mar 20th, 2002, 12:12 PM	#10
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Please read above suggestion or use cell reference for criteria =B21-SUMPRODUCT((E3:E17=E3)*(D3:D17)) Change the E3 to cell that contains your criteria!