IF calculation problem

JRRT

New Member
Joined
Mar 19, 2002
Messages
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?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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,))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
=B21-SUMPRODUCT((E3:E17="Pioneer 33r87")*(D3:D17))

or use cell reference for criteria
=B21-SUMPRODUCT((E3:E17=E3)*(D3:D17))
 
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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
Back
Top