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,

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,

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,

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,