Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF Statement
I am trying to write a formula charging the first 400 tickets $5 each. But as
soon as the 401st is sold, I need it to be $8. Tickets are not being sold one at a time. This is for a family event, so it could be 5 tickets at a time. The current fomulas I have =IF(A4<=400,(E4+F4)*5+G4*2,(E4+F4)*8+G4*2) will charge $8 a ticket, if a family of 4 are tickets 398, 399, 400, and 401. I need the first three to be $5 each, and one ticket to be $8. Where A4 is a cummulative column (A5's formula is =SUM(A4+E5+F5)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF Statement
=Min(400,a4)*5+max(0,a4-400)*8
"Jonni" wrote: I am trying to write a formula charging the first 400 tickets $5 each. But as soon as the 401st is sold, I need it to be $8. Tickets are not being sold one at a time. This is for a family event, so it could be 5 tickets at a time. The current fomulas I have =IF(A4<=400,(E4+F4)*5+G4*2,(E4+F4)*8+G4*2) will charge $8 a ticket, if a family of 4 are tickets 398, 399, 400, and 401. I need the first three to be $5 each, and one ticket to be $8. Where A4 is a cummulative column (A5's formula is =SUM(A4+E5+F5)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF Statement
Something still doesn't work, or I am having a braindead moment,
My cummulative column is A4, so initially A4 = E4 + F4 A5 = (A4 + E5 + F5) and so on. My total column now multiplies A5*5. I need it to mutliply (E5 + F5)*5 unless one of the values makes the total greater than 400. "Brad" wrote: =Min(400,a4)*5+max(0,a4-400)*8 "Jonni" wrote: I am trying to write a formula charging the first 400 tickets $5 each. But as soon as the 401st is sold, I need it to be $8. Tickets are not being sold one at a time. This is for a family event, so it could be 5 tickets at a time. The current fomulas I have =IF(A4<=400,(E4+F4)*5+G4*2,(E4+F4)*8+G4*2) will charge $8 a ticket, if a family of 4 are tickets 398, 399, 400, and 401. I need the first three to be $5 each, and one ticket to be $8. Where A4 is a cummulative column (A5's formula is =SUM(A4+E5+F5)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF Statement
Lets deal with numbers
a4 = 390 e5 = 9 f5 = 2 if I understand your question correctly a5 = a4 + e5 + f5 Then somewhere (assume in {b5} you're multipling {a5} by 5 or 8 or a mixture of the two) in B5=Min(400,a5)*5+max(0,a5-400)*8 Min(400,a5)=Min(400,401)=400*5=2000 Max(0,401-400)=1*8 = 8 Therefore B5 = 2008 and b4 = 1950 = (390*5) the cost for the family would be 2008-1950 = 58 If I knew more I could have a better answer - but this works. -- Wag more, bark less "Jonni" wrote: Something still doesn't work, or I am having a braindead moment, My cummulative column is A4, so initially A4 = E4 + F4 A5 = (A4 + E5 + F5) and so on. My total column now multiplies A5*5. I need it to mutliply (E5 + F5)*5 unless one of the values makes the total greater than 400. "Brad" wrote: =Min(400,a4)*5+max(0,a4-400)*8 "Jonni" wrote: I am trying to write a formula charging the first 400 tickets $5 each. But as soon as the 401st is sold, I need it to be $8. Tickets are not being sold one at a time. This is for a family event, so it could be 5 tickets at a time. The current fomulas I have =IF(A4<=400,(E4+F4)*5+G4*2,(E4+F4)*8+G4*2) will charge $8 a ticket, if a family of 4 are tickets 398, 399, 400, and 401. I need the first three to be $5 each, and one ticket to be $8. Where A4 is a cummulative column (A5's formula is =SUM(A4+E5+F5)) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF Statement
Assuming the data start in row 2 (not row 5) this is one alternative that
will work. =IF(A2<=400,(E2+F2)*5,IF(A1<=400,(400-A1)*5+(A2-400)*8,(E2+F2)*8))+G2*2 -- Wag more, bark less "Jonni" wrote: Something still doesn't work, or I am having a braindead moment, My cummulative column is A4, so initially A4 = E4 + F4 A5 = (A4 + E5 + F5) and so on. My total column now multiplies A5*5. I need it to mutliply (E5 + F5)*5 unless one of the values makes the total greater than 400. "Brad" wrote: =Min(400,a4)*5+max(0,a4-400)*8 "Jonni" wrote: I am trying to write a formula charging the first 400 tickets $5 each. But as soon as the 401st is sold, I need it to be $8. Tickets are not being sold one at a time. This is for a family event, so it could be 5 tickets at a time. The current fomulas I have =IF(A4<=400,(E4+F4)*5+G4*2,(E4+F4)*8+G4*2) will charge $8 a ticket, if a family of 4 are tickets 398, 399, 400, and 401. I need the first three to be $5 each, and one ticket to be $8. Where A4 is a cummulative column (A5's formula is =SUM(A4+E5+F5)) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF Statement
I can see that it works when you write it down, but it's still not working
when I put it on the spreadsheet. Numbers: e4 = 4 f4 = 2 a4 = 6 = e4 + f4 i4 = (MIN(400,A4)*5+MAX(0,A4-400)*8) = (MIN(400,6)*5+MAX(0,-394)*8) = 30 + 0, so for a family of 6, it will be $30. e5 = 2 f5 = 0 a5 = a4 + e5 + f5 = 8 i5 = (MIN(400,A5)*5+MAX(0,A5-400)*8) = (MIN(400,8)*5 + MAX(0,-392)*8) = 40 + 0. So for this family of two, according to the formula, it will cost them $40 instead of $10. e6 = 392 f6 = 1 a6 = a5 + e6 + f6 = 401 i6 = (MIN(400,A6)*5+MAX(0,A6-400)*8) = (MIN(400,401)*5+MAX(0,1)*8) = 400*5 + 8 = 2008 should be $1968 "Brad" wrote: Lets deal with numbers a4 = 390 e5 = 9 f5 = 2 if I understand your question correctly a5 = a4 + e5 + f5 Then somewhere (assume in {b5} you're multipling {a5} by 5 or 8 or a mixture of the two) in B5=Min(400,a5)*5+max(0,a5-400)*8 Min(400,a5)=Min(400,401)=400*5=2000 Max(0,401-400)=1*8 = 8 Therefore B5 = 2008 and b4 = 1950 = (390*5) the cost for the family would be 2008-1950 = 58 If I knew more I could have a better answer - but this works. -- Wag more, bark less "Jonni" wrote: Something still doesn't work, or I am having a braindead moment, My cummulative column is A4, so initially A4 = E4 + F4 A5 = (A4 + E5 + F5) and so on. My total column now multiplies A5*5. I need it to mutliply (E5 + F5)*5 unless one of the values makes the total greater than 400. "Brad" wrote: =Min(400,a4)*5+max(0,a4-400)*8 "Jonni" wrote: I am trying to write a formula charging the first 400 tickets $5 each. But as soon as the 401st is sold, I need it to be $8. Tickets are not being sold one at a time. This is for a family event, so it could be 5 tickets at a time. The current fomulas I have =IF(A4<=400,(E4+F4)*5+G4*2,(E4+F4)*8+G4*2) will charge $8 a ticket, if a family of 4 are tickets 398, 399, 400, and 401. I need the first three to be $5 each, and one ticket to be $8. Where A4 is a cummulative column (A5's formula is =SUM(A4+E5+F5)) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF Statement
This worked!!!
THANK YOU!!! "Brad" wrote: Assuming the data start in row 2 (not row 5) this is one alternative that will work. =IF(A2<=400,(E2+F2)*5,IF(A1<=400,(400-A1)*5+(A2-400)*8,(E2+F2)*8))+G2*2 -- Wag more, bark less "Jonni" wrote: Something still doesn't work, or I am having a braindead moment, My cummulative column is A4, so initially A4 = E4 + F4 A5 = (A4 + E5 + F5) and so on. My total column now multiplies A5*5. I need it to mutliply (E5 + F5)*5 unless one of the values makes the total greater than 400. "Brad" wrote: =Min(400,a4)*5+max(0,a4-400)*8 "Jonni" wrote: I am trying to write a formula charging the first 400 tickets $5 each. But as soon as the 401st is sold, I need it to be $8. Tickets are not being sold one at a time. This is for a family event, so it could be 5 tickets at a time. The current fomulas I have =IF(A4<=400,(E4+F4)*5+G4*2,(E4+F4)*8+G4*2) will charge $8 a ticket, if a family of 4 are tickets 398, 399, 400, and 401. I need the first three to be $5 each, and one ticket to be $8. Where A4 is a cummulative column (A5's formula is =SUM(A4+E5+F5)) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF Statement
Glad to help.
-- Wag more, bark less "Jonni" wrote: This worked!!! THANK YOU!!! "Brad" wrote: Assuming the data start in row 2 (not row 5) this is one alternative that will work. =IF(A2<=400,(E2+F2)*5,IF(A1<=400,(400-A1)*5+(A2-400)*8,(E2+F2)*8))+G2*2 -- Wag more, bark less "Jonni" wrote: Something still doesn't work, or I am having a braindead moment, My cummulative column is A4, so initially A4 = E4 + F4 A5 = (A4 + E5 + F5) and so on. My total column now multiplies A5*5. I need it to mutliply (E5 + F5)*5 unless one of the values makes the total greater than 400. "Brad" wrote: =Min(400,a4)*5+max(0,a4-400)*8 "Jonni" wrote: I am trying to write a formula charging the first 400 tickets $5 each. But as soon as the 401st is sold, I need it to be $8. Tickets are not being sold one at a time. This is for a family event, so it could be 5 tickets at a time. The current fomulas I have =IF(A4<=400,(E4+F4)*5+G4*2,(E4+F4)*8+G4*2) will charge $8 a ticket, if a family of 4 are tickets 398, 399, 400, and 401. I need the first three to be $5 each, and one ticket to be $8. Where A4 is a cummulative column (A5's formula is =SUM(A4+E5+F5)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex IF THEN statement | Excel Worksheet Functions | |||
Complex If/And Statement | Excel Worksheet Functions | |||
Complex IF STATEMENT | Excel Discussion (Misc queries) | |||
Help with complex If statement | Excel Worksheet Functions | |||
complex IF(OR(...AND())) statement | Excel Discussion (Misc queries) |