 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / May 2008Excel - Multiple IF Criteria
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. > I am creating a spreadsheet that tracks projected revenue based on product > and age. [quoted text clipped - 13 lines] > 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)) 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. > While your example is clear, it's not clear to me how your factors > relate. > Product determines percentage of profit which are the numbers in the formulas > (e.g. .055) [quoted text clipped - 7 lines] > 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... 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 > > Product determines percentage of profit which are the numbers in the formulas > > (e.g. .055) [quoted text clipped - 9 lines] > > Sounds like what my suggestion does... 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 > I think I need to make things a bit clearer. > [quoted text clipped - 14 lines] > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.