Excel Running Total Formula

Hi:

I have a spreadsheet where Marketing dollars are determined from Sales figures. 20% of Sales should be allotted each month toward Marketing, but not to exceed $150,000 for the year. I need the monthly Marketing figures in a row, determined by the running formula based on Sales.

I have attached an example Excel spreadsheet.

Thanks in advance! ExpertsExchange.xls
Cactus1994OwnerAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
A correction.

Formula in B4:

   =MIN(B3*0.2,150000)

Formula in C4:

   =MIN(C3*0.2,MAX(150000-SUM($B4:B4),0))

Copy C4 to the right.

Kevin
ExpertsExchange.xls
0
 
zorvek (Kevin Jones)ConsultantCommented:
See attached.

Kevin
ExpertsExchange.xls
0
 
Cactus1994OwnerAuthor Commented:
Kevin:

Wow. Great. And quick, too.

One question, however. What if January sales were, for example, $1,000,000 and the Marketing dollars derived from that was $200,000? That would exceed the $150.000 cap....

Thanks.
0
 
zorvek (Kevin Jones)ConsultantCommented:
See my second post above.

Kevin
0
 
Cactus1994OwnerAuthor Commented:
Thanks, Kevin. I posted my followup question before I saw you'd responded with a correction.

Works perfectly. Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.