If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|