Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Start Dates based on Need-By Date? | Excel Worksheet Functions | |||
Calculation based with Range of Date | New Users to Excel | |||
FILL DATES IN VARIOUS CELLS BASED ON A DATE ENTERED IN A SINGLE C. | Excel Discussion (Misc queries) | |||
LINK ONE ROW BASED ON CONTENTS OF A COLUMN WITHIN THE ROW (DATE) | Excel Worksheet Functions | |||
Counting an Array based on a calculation | Excel Discussion (Misc queries) |