#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 846
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 846
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 846
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 846
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Complex IF THEN statement Greg S Excel Worksheet Functions 5 December 9th 08 12:40 AM
Complex If/And Statement jonssmaster Excel Worksheet Functions 8 December 1st 08 08:27 PM
Complex IF STATEMENT CR3 Excel Discussion (Misc queries) 3 December 6th 07 01:32 PM
Help with complex If statement GHawkins[_2_] Excel Worksheet Functions 2 September 6th 07 10:16 PM
complex IF(OR(...AND())) statement Dave F Excel Discussion (Misc queries) 2 October 4th 06 06:24 PM


All times are GMT +1. The time now is 01:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"