If date range, then % commission. [Closed]

Report
Posts
3
Registration date
Monday April 7, 2014
Status
Member
Last seen
April 8, 2014
-
 Blocked Profile -
I am trying to create a formula that will look at a group of rows and calculate a percentage of a particular date range of money. The rows have a date and, sometimes, a dollar amount.

Column A = Date
Column B = Cash received
Column C = Balance

20 Rows of data with dates ranging from Jan 1 2014 to March 31 2014. I am looking for one cell to represent 5% of total cash received during Jan 1 - Feb 26. Then I am looking for another cell to represent 3% of total cash received between Feb 27 - March 15.

I hope this makes sense.

5 replies


Ok, so do this, it you need further explaination, let us know:

=SUM(A1:A18)*0.05
=SUM(A19:A30)*0.03


It really is that simple!

If you need help delimiting the date, then you need to become familiar with countif.

Or you could always sort by date?

Have FUN!

"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon
Posts
3
Registration date
Monday April 7, 2014
Status
Member
Last seen
April 8, 2014

This method will not work in this case. The spreadsheet will be used 80 times with different data every time. There needs to be a way to search a range, identify rows with a date in range, add the cash received and multiply by the %.

Oh, so you are looking for a form, to input the date to look for?

Like a search form?


Posts
3
Registration date
Monday April 7, 2014
Status
Member
Last seen
April 8, 2014

I was not aware of the term "Search Form", but that seems to describe what I am looking for.

Ok, you say that there needs to be a search, based on what, the day?

If your formulas reference the current day would that matter? I am not understanding, how you wish to have this "task" started?

In your formula, qualify the amount with an if statement:

Like this:
=IF(A1<2/28/2013,B1*0.05,B1*0.03)



"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon