Calculation based on date
 

I want to work out quarterly planned and actual sales figures with minimal
data entry.

I have my profit which is a rolling sum, i.e. every new sale adds to the
total. What i want is to perform a calculation based on the date on my
profit. When it is the end of the first quarter my calculation is performed
and the results appear. This works fine. What i then want to do is freeze
that result so that it does not update when i make my next sale so that i can
look back and see where i was last quarter. Any ideas on how to do this? I
have tried to simplify it, but what i want is to see at the end of the year
what my sales profit was at the end of each quarter without lots of data
entry.

Thanks,

Calculation based on date
 

Hi Jack. I put a little spreadsheet together to track sales and summarize
by quarter. In Column A I entered the date of the sale and in Column B the
amount. In Column C, I entered the following formula:

=IF(month(A2)<3,1,IF(month(A2)<7,2,IF(month(A2)<10 ,3,4)))

This will put a 1,2,3 or 4 in Column C based on the Dates in Column A. At
any time, not just the end of the year, you can use the Subtotal function on
the Data menu, choosing to Subtotal based on the change in Quarter. At the
end of the year you will have a Subtotal for each Quarter and a total for the
Year. HTH

--
Sincerely, Michael Colvin


"Jack" wrote:

I want to work out quarterly planned and actual sales figures with minimal
data entry.

I have my profit which is a rolling sum, i.e. every new sale adds to the
total. What i want is to perform a calculation based on the date on my
profit. When it is the end of the first quarter my calculation is performed
and the results appear. This works fine. What i then want to do is freeze
that result so that it does not update when i make my next sale so that i can
look back and see where i was last quarter. Any ideas on how to do this? I
have tried to simplify it, but what i want is to see at the end of the year
what my sales profit was at the end of each quarter without lots of data
entry.

Thanks,

Calculation based on date
 

Oops. Sorry Jack, typo alert - in the 1st IF statement, it should be A2<4,
not A2<3.
--
Sincerely, Michael Colvin


"Jack" wrote:

I want to work out quarterly planned and actual sales figures with minimal
data entry.

I have my profit which is a rolling sum, i.e. every new sale adds to the
total. What i want is to perform a calculation based on the date on my
profit. When it is the end of the first quarter my calculation is performed
and the results appear. This works fine. What i then want to do is freeze
that result so that it does not update when i make my next sale so that i can
look back and see where i was last quarter. Any ideas on how to do this? I
have tried to simplify it, but what i want is to see at the end of the year
what my sales profit was at the end of each quarter without lots of data
entry.

Thanks,

Calculation based on date
 

Thanks for the input Michael, i have used similar in other spreadsheets and
simply forgot about it yesterday. Thanks for the reminder i have been able to
adapt this for my needs.

Cheers.

"Michael" wrote:

Oops. Sorry Jack, typo alert - in the 1st IF statement, it should be A2<4,
not A2<3.
--
Sincerely, Michael Colvin


"Jack" wrote:

I want to work out quarterly planned and actual sales figures with minimal
data entry.

I have my profit which is a rolling sum, i.e. every new sale adds to the
total. What i want is to perform a calculation based on the date on my
profit. When it is the end of the first quarter my calculation is performed
and the results appear. This works fine. What i then want to do is freeze
that result so that it does not update when i make my next sale so that i can
look back and see where i was last quarter. Any ideas on how to do this? I
have tried to simplify it, but what i want is to see at the end of the year
what my sales profit was at the end of each quarter without lots of data
entry.

Thanks,