Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   IF formula solution (https://www.askmehelpdesk.com/showthread.php?t=278539)

  • Nov 8, 2008, 01:35 PM
    charisio
    IF formula solution
    I have this pricing structure:

    items 0-10 price is 10 . So the first 10 items are 100.
    items 11-50 price is 8 .So is 100 for the first and 40x8=320 ,total 420
    items 51-100 price is 6.So 100+320=420 and 50x6=300.If someone buys 100 will pay 720.
    After the 100 items the price is 5 and here is the end.
    I need a formula that I will put a number in a cell ,like 55.That means that it will show the number 450.because.:10x10=100 +40x8=320 +5x6=30.
    We add and 100+320+30=450.
    There is a problem with this one:
    =IF(A1<11,A1*10)+IF(AND(A1>10,A1<52),100+(A1-10)*8)+IF(AND(A1>51,A1<101),100+320+(A1*6))+IF(A1> 100,720+(A1*5))
    When I put the number 55 I get 750.
  • Nov 8, 2008, 02:16 PM
    McWilliams58

    I suggest reversing the order of the tests on the number of items. This will let you nest the IF statements. Try
    =IF(A1>100,(A1-100)*5+50*6+40*8+10*10,IF(A1>50,(A1-50)*6+40*8+10*10,IF(A1>10,(A1-10)*8+10*10,A1*10)))
    It should work for what you are doing. Of course you could also use variables in place of the prices of 5, 6, 8, and 10 which would allow changes in your pricing structure to be evaluated. Good Luck.
  • Nov 8, 2008, 02:40 PM
    JBeaucaire

    Well, this will work, less confusing:

    =IF(A1>100,((A1-100)*5)+720,IF(A1>50,((A1-50)*6)+420,IF(A1>10,((A1-10)*8)+100,A1*10)))

    EDIT: noticed answer above... LOL, mine hardcodes the fixed cost above each tier to read more easily. But I personally like the idea of putting the individual prices on the sheet somewhere you can change them and the price structure updates itself.

    For instance:

    AA1 = 10
    AA2 = 8
    AA3 = 6
    AA4 = 5

    Code changes to:
    =IF(A1>100,((A1-100)*AA4)+(AA1*10)+(AA2*40)+(AA3*50),IF(A1>50,((A1-50)*AA3)+(AA1*10)+(AA2*40),IF(A1>10,((A1-10)*AA2)+(AA1*10),A1*AA1)))
  • Nov 8, 2008, 02:50 PM
    JBeaucaire
    Also, an array would be easiest of all.

    =SUMPRODUCT(--(A1>{0,10,50,100}),A1-{0,10,50,100},{10,-2,-2,-1})

    This is short and sweet, and if you change the price structure, just change the last { } section. It shows the reductions. 10, -2 (means 8), -2 (means 6), -1 (means 5)}

  • All times are GMT -7. The time now is 11:10 AM.