Using the IF Function to calculate cost of products having 3 possible configuration and various quantities?

Posted on

QUESTION :

How do I use the IF function to calculate the cost of cover for each book type and quantity?

Number     Type of       Cost of       Correct Answer is                   
of Copies    Cover:       Cover   
           Type 0=$1, 
           Type 1=€1,55, 
           Type 2=$2               
125       2              $250,00 
145       1              $224,75 
425       0              $425,00   
250       2              $500,00 
45        0              $45,00 
1.000     2              $2.000,00 
550       1              $852,50 
450       0              $450,00 
450       1              $697,50 

Thanks.

ANSWER :

If statements can be nested, the statement you need is

=IF(B2=0,A2*1,IF(B2=1,A2*1.55,IF(B2=2,A2*2,)))

The syntax for the if statement is:

If(test, evaluate if true, evaluate if false)

You essentially nest the tests in the false condition and do your multiplication in the true conditions.

if(this, then do this, else if(this, then do this, else if(this, then do this,else nothing)))

To do the formatting for currencies you may want to look into Conditional Formatting

I have made a conditional format ruleset that works for your data as well:

enter image description here

Leave a Reply

Your email address will not be published.