Help! Need to Count up to a maximum number, and multiple by price

J

JzLim

New Member
Joined
Oct 17, 2014
Messages
8
Hi there,

I need some help with a formula, been trying to figure this out but can't seem to get it right (Preferably no VBA).

Tickets.jpg
[/URL][/IMG]

I need to calculate the expected revenue of ticket sales. Few things to note:
1.) Ticket sales start from cheapest to most expensive so once the $200 one is sold out, the $300 tickets will be on sale next.
2.) Expected sales is 80% of the total number of tickets.

Is there a way to calculate this via a single formula?
So if my expected sales of 225 (Event A), it would count up till the $700 price point with 4 tickets sold at said price point. Hence, expected revenue would be tickets sold (number) x price it was sold at ($).

Any ideas? Any help would be much appreciated!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
See if this gives the expected results, must be array confirmed with Ctrl Shift Enter.

PHP: 
=SUMPRODUCT(OFFSET(C$11,,,-MATCH(FALSE,SUBTOTAL(9,OFFSET(C$11,,,-ROW(C$1:C$10),1))>C$14),1),OFFSET($B$11,,,-MATCH(FALSE,SUBTOTAL(9,OFFSET(C$11,,,-ROW(C$1:C$10),1))>C$14),1))+((C$14-SUM(OFFSET(C$11,,,-MATCH(FALSE,SUBTOTAL(9,OFFSET(C$11,,,-ROW(C$1:C$10),1))>C$14))))*OFFSET($B$11,-MATCH(FALSE,SUBTOTAL(9,OFFSET(C$11,,,-ROW(C$1:C$10),1))>C$14),0))
 
Upvote 0
jasonb75 said:
See if this gives the expected results, must be array confirmed with Ctrl Shift Enter.

PHP: 
=SUMPRODUCT(OFFSET(C$11,,,-MATCH(FALSE,SUBTOTAL(9,OFFSET(C$11,,,-ROW(C$1:C$10),1))>C$14),1),OFFSET($B$11,,,-MATCH(FALSE,SUBTOTAL(9,OFFSET(C$11,,,-ROW(C$1:C$10),1))>C$14),1))+((C$14-SUM(OFFSET(C$11,,,-MATCH(FALSE,SUBTOTAL(9,OFFSET(C$11,,,-ROW(C$1:C$10),1))>C$14))))*OFFSET($B$11,-MATCH(FALSE,SUBTOTAL(9,OFFSET(C$11,,,-ROW(C$1:C$10),1))>C$14),0))
Click to expand...

There is an error when i try to confirm the array with CSE, can't seem to get it to work!
 
Upvote 0
Might have been a copy error, see if it works from this one,

Excel Workbook
C
1597000
Sheet1
 
Upvote 0
Redid it (removed the spaces) and it worked! Cept that the value for the first Event was off by $140, any idea why?
Actual value: $97,000
Value returned: $96,860

This only happened to column C, Event A. Thanks!
 
Upvote 0
Where did you remove spaces from? I can't see any in either formula :confused:

Have you changed any of the ranges or moved the data to different rows? I get 97,000 for column C, not sure why your result should be any different.
 
Upvote 0
My bad, copied some "Spaces" when I was copy pasting the formula :rolleyes:

Going to double check it soon, will update here when done!
 
Upvote 0
I found the problem, my columns were set to zero decimal places, the expected sales of 225 was actually (224.8) ruining the expected revenue. Formula works like a charm! Many thanks :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Calculate units needed for price reduction
Replies
2
Views
299
Caly
C
M
  • Question
Logical Help Needed
Replies
3
Views
96
Cubist
C
G
  • Question
Gross Deal Qty and Revenue Spread into Monthly Values?
Replies
0
Views
306
GaryV
G
R
  • Question
IF Function Common To Work with multiple combination of two negative & positive numbers
Replies
5
Views
138
Micron
M
M
  • Solved
Formula for tiered commission on different quantity/price levels
2 3 4
Replies
30
Views
3K
KRice
K

Forum statistics

Threads
1,214,591
Messages
6,120,427
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top