# 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.

###### Who is Participating?

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

ConsultantCommented:
See attached.

Kevin
ExpertsExchange.xls
0

OwnerAuthor 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

ConsultantCommented:
See my second post above.

Kevin
0

OwnerAuthor 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.