Excel - Multiple IF Criteria

[Brian]

I am creating a spreadsheet that tracks projected revenue based on product
and age.

F5=Product (Apple, Orange, etc) Each product has a different profit margin
G5=Price
H5=Customer Age
I5=Projected Revenue

Age is the deciding factor to calculate costs. My forumla without age is:

I5=IF(F5="Apple",G5*0.055,IF(F5="Orange",G5*0.05,))

My attempted formula with age is:
I5=IF(F5="Apple",IF(H5<=80,G5*0.055,IF(H5>=86,G5*0.0275,G5*.044)))

What am I doing wrong? Should I be using a different formula? Thank you for
your help.

 

[JE McGimpsey]

I am creating a spreadsheet that tracks projected revenue based on product
and age.

F5=Product (Apple, Orange, etc) Each product has a different profit margin
G5=Price
H5=Customer Age
I5=Projected Revenue

Age is the deciding factor to calculate costs. My forumla without age is:

I5=IF(F5="Apple",G5*0.055,IF(F5="Orange",G5*0.05,))

My attempted formula with age is:
I5=IF(F5="Apple",IF(H5<=80,G5*0.055,IF(H5>=86,G5*0.0275,G5*.044)))

What am I doing wrong? Should I be using a different formula? Thank you for
your help.

While your example is clear, it's not clear to me how your factors
relate.

However, I'd set this up as a lookup table. For instance, on a separate
sheet (say, "Sheet2"):

A B C D
1 Cust Age
2 Product 0 80.00001 86
3 Apple 5.5% 4.4% 2.75%
4 Orange 5.0% 4.0% 2.5%


Then

I5: =G5 * INDEX(Sheet2!$B$2:$D$10, MATCH(F5, Sheet2!$A$2:$A$10,
FALSE), MATCH(H5, Sheet2!$B$2:$D$2, TRUE))

 

[Brian]

Product determines percentage of profit which are the numbers in the formulas
(e.g. .055)

Customer age also effects the profit as we charge customers less for certain
age groups.

Each product canhave a different rate for varying age ranges (e.g. 60-75 or
80-85.)

So, we need a formula that will take into consideration both of these
factors when calculating the projected revenue.

 

[JE McGimpsey]

Product determines percentage of profit which are the numbers in the formulas
(e.g. .055)

Customer age also effects the profit as we charge customers less for certain
age groups.

Each product canhave a different rate for varying age ranges (e.g. 60-75 or
80-85.)

So, we need a formula that will take into consideration both of these
factors when calculating the projected revenue.

Sounds like what my suggestion does...

 

[Brian]

I'm a bit confused by the layout of your example. Is this what you mean?

A:1 Cust. Age B:1 "BLANK" C:1 "BLANK" D:1 "BLANK"
A:2 Product B:2 0 C: 2 80.00001 D:2 86
A:3 Apple B:3 5.5% C: 3 4.4% D:3 2.75%
A:3 Oranage B:3 5.0% C: 3 4.0% D:3 2.5%

I'm a little confused by the placement of the age and products in the table.

Thanks again,
Brian

 

[Brian]

I think I need to make things a bit clearer.

There are currently 7 products. We receive a commission on each product
sold. The commission is based on the age of the client. The age bands and
commissions vary from product to product.

Would the example need to be set up as follows with each product listed once
for each variance in age/commission?

Product Age Commission
Product 1 0-80 0.055
Product 1 81-85 0.044
Product 1 86-90 0.0275
Product 2 0-75 0.045
Product 2 76-80 0.0425
Product 2 81-85 0.02
Product 3 0-80 0.03
Product 3 81-85 0.015

 

[JE McGimpsey]

I think I need to make things a bit clearer.

There are currently 7 products. We receive a commission on each product
sold. The commission is based on the age of the client. The age bands and
commissions vary from product to product.

Would the example need to be set up as follows with each product listed once
for each variance in age/commission?

Product Age Commission
Product 1 0-80 0.055
Product 1 81-85 0.044
Product 1 86-90 0.0275
Product 2 0-75 0.045
Product 2 76-80 0.0425
Product 2 81-85 0.02
Product 3 0-80 0.03
Product 3 81-85 0.015

That would be more difficult than what I suggested, as long as your age
bands are consistent.

For instance, setting up a table of the above:

A B C D E
1 PRODUCT 0 76 81 86
2 Product 1 5.5% 5.5% 4.4% 2.75%
3 Product 2 4.5% 4.25% 2% 2%
4 Product 3 3% 3% 1.5% 1.5%

There are other ways of setting it up, of course. If your age bands
aren't consistent, you would be better to set up individual tables of
products and ages.