Ask Experts Questions for FREE Help !
Ask
    charisio's Avatar
    charisio Posts: 1, Reputation: 1
    New Member
     
    #1

    Nov 8, 2008, 01:35 PM
    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.
    McWilliams58's Avatar
    McWilliams58 Posts: 5, Reputation: 2
    New Member
     
    #2

    Nov 8, 2008, 02:16 PM

    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #3

    Nov 8, 2008, 02:40 PM

    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)))
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Nov 8, 2008, 02:50 PM
    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)}

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Looking for a solution [ 4 Answers ]

He doesn't call when he say he will and he usually only rings me about once a month. I know this doesn't look good and I should give up on him but I really like this guy and the interest was there to begin with. Is there anything at all I could do to try and make him want me again or is this a...

What is the best solution for Ed? [ 2 Answers ]

How does Vevitra compare with Levitra?

What is a solution [ 3 Answers ]

My husband spends his time and money on friends only drinking. He is out from morning and returns home at night. If I ask him why he spends time with his friends he gets angry and states that --He should not stick all the time to his wife. He does not have any responsibilities even towards his...

BTB 1 % solution [ 1 Answers ]

How to prepare bromomotymol blue aquaous solution 1 % ?


View more questions Search