Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
I have an excel spreadsheet, where i keep track of how many estimates were
sent out by our estimators, the date it was sent out, approved, started, completed, invoiced etc... My boss wants to know how many $ worth of estimates was sent out by each estimator every week, running month, last month, running quarter, last quarter, running year and last year. Any easy way to set this up that excel automatically calculates all this. Any help would be appreciated. Thanks :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
A thing like this is made for Pivot Tables
http://www.contextures.com/xlfaqPivot.html -- Regards, Peo Sjoblom "Saadia" wrote in message ... I have an excel spreadsheet, where i keep track of how many estimates were sent out by our estimators, the date it was sent out, approved, started, completed, invoiced etc... My boss wants to know how many $ worth of estimates was sent out by each estimator every week, running month, last month, running quarter, last quarter, running year and last year. Any easy way to set this up that excel automatically calculates all this. Any help would be appreciated. Thanks :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
Hi,
Here is the basic idea, you would create formulas for each of the summary calculations: =SUMPRODUCT(--(A1:A13=D1),--(B1:B13TODAY()-7),--(B1:B13<=TODAY()),C1:C13) This is the formula to caluculate the week, depending on how you define last week. The estimators are listed in A1:A13 with the one you want to calculate for in D1. The dates are in B1:B13 and the amount in C1:C13. This is just an example, which you would need to modify for all the calculations you want. If this helps, please click the yes button Cheers, Shane Devenshire "Saadia" wrote: I have an excel spreadsheet, where i keep track of how many estimates were sent out by our estimators, the date it was sent out, approved, started, completed, invoiced etc... My boss wants to know how many $ worth of estimates was sent out by each estimator every week, running month, last month, running quarter, last quarter, running year and last year. Any easy way to set this up that excel automatically calculates all this. Any help would be appreciated. Thanks :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
Thanks,
I will try these options out and see if i can set it up :) "Shane Devenshire" wrote: Hi, Here is the basic idea, you would create formulas for each of the summary calculations: =SUMPRODUCT(--(A1:A13=D1),--(B1:B13TODAY()-7),--(B1:B13<=TODAY()),C1:C13) This is the formula to caluculate the week, depending on how you define last week. The estimators are listed in A1:A13 with the one you want to calculate for in D1. The dates are in B1:B13 and the amount in C1:C13. This is just an example, which you would need to modify for all the calculations you want. If this helps, please click the yes button Cheers, Shane Devenshire "Saadia" wrote: I have an excel spreadsheet, where i keep track of how many estimates were sent out by our estimators, the date it was sent out, approved, started, completed, invoiced etc... My boss wants to know how many $ worth of estimates was sent out by each estimator every week, running month, last month, running quarter, last quarter, running year and last year. Any easy way to set this up that excel automatically calculates all this. Any help would be appreciated. Thanks :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
Hi Saadia
A pivot table would probably be your best bet. Look at Data... Pivot Table & Pivot Chart report Best rgds Chris Lav "Saadia" wrote in message ... I have an excel spreadsheet, where i keep track of how many estimates were sent out by our estimators, the date it was sent out, approved, started, completed, invoiced etc... My boss wants to know how many $ worth of estimates was sent out by each estimator every week, running month, last month, running quarter, last quarter, running year and last year. Any easy way to set this up that excel automatically calculates all this. Any help would be appreciated. Thanks :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |