Excel Calculations How to Continue Them
May 25, 2022 at 6:44 am OVERALL DESIGN COMMENT:
We will have source data for different projects in the following format: date, name, hours, amount with as many as 20,000 line items and possibly as many
as 500 names.
we want to input data into Number 1 spreadsheet only. Spreadsheets 2,3 and 4 will automatically compute each time data is input into spreadsheet number 1. We do not
want to have to modify, change or add to spreadsheets 2,3 and 4. More specifically:
1.We have 4 columns of input (date, name, hours, amount) for our spreadsheets. Up to 20,000 different line items covering 2 years. There may be as many as 6 line items for any given year/ month for each name.
In line 1 we would like to INPUT: ORGANIZATION NAME:
2. Using the source data input (above) we need to make another spreadsheet adding the following 3 fields: (year, quarter, month number (1 thru 12) to each line item in spreadsheet 1. This would show the source date plus the 3 new fields AND automatically calculate each time we add data in Number 1 above. Line1 would show the organization name.
EXAMPLE:
ORGANIZATION NAME XXXXXXX
Year quarter month date name hours amount
2020 1 1 01/07/2020 james smith 40.00 600.00
2020 1 1 01/14/2020 james smith 20.00 300.00
2020 1 1 01/21/2020 james smith 40.00 600.00
2020 1 1 01/28/2020 james smith 30.00 450.00
2020 2 2 02/07/2020 james smith 40.00 600.00
2020 2 2 02/14/2020 james smith 10.00 150.00
2020 2 2 02/21/2020 james smith 10.00 150.00
2020 2 2 02/28/2020 james smith 20.00 300.00
3. Using the modified source data in #2 above, we want a spreadsheet that shows:
one line item of names that have over 130 hours in any given month displayed in the following format that totals the hours and amount for each name for any given month.
year, quarter number (1 thru 4), month (1 thru 12) , name, total hours for that month only, total amount applicable to that month. In other words Only for those names that have 130 hours OR MORE in any given year / month , we want to see one line that shows: year, quarter number, month number, name, total hours for that particular month only, and total amount for that month only. Line1 would show the organization name.
EXAMPLE:
ORGANIZATION NAME XXXXXXX
Year quarter month name hours amount
2020 1 1 james smith 130.00 1950.00 (this is the total for 2020/month 1)
(james smith would not be listed for 2020 quarter 1 month 2 because he only worked 80.00 hours for that month)
4. Using the modified source data in #2 above, we want a spreadsheet that ONLY shows:
one line item of names that have dates that are greater than or equal to March 12, 2020 and less that or equal to March 31, 2020 displayed in the following format:
year, quarter number (1 thru 4), month (1 thru 12), chartered field inserted "first quarter", name, total hours for that time period only, total amount applicable to that month.
5. Using Spreadsheet Number 3 date only, display the following:
Year, quarter, Total hours for each quarter that quarter, total amount for that quarter
Line1 would show the organization name.
This spreadsheet would have only 12 line items example below:
ORGANIZATION NAME xxxxxxxxxxxxxxx
Year quarter hours amount
2020 1 1236.4 100,110.12
2020 2 934.2 71.123.11
2020 3 1231.1 110.456.23
2020 4 837.1 81,555.41
2021 1 1174.3
OVERALL COMMENTS:
There will be more engagements to modify further once this first series of spreadsheets are completed and tested.
For purposes which will be stated later, we do not want the source data changed and we want the display to be seen separately in spreadsheet 2,3,4 and 5 and kept separate.
Source: https://www.ablebits.com/office-addins-blog/2017/06/21/how-to-do-calculations-excel/
0 Response to "Excel Calculations How to Continue Them"
Post a Comment