Forum Discussion
Variance and Percentage between MIN MAX Values
Hey, I would be lucky to help, but I have to admit that I do not fully understand what you are looking for. For this reason, please create sample data in Excel, create sample calculations. Upload the Excel file to onedrive or dropbox and share the link.
Regards,
Tom
Great thanks Tom, attached is spreadsheet - I am after a calculation that will provide the difference between the Regions based off the highest value returned - RegionTechVariance
Please let me know if you have any further questions
Hi,
In range K3:K6, you have taken the denominator as 17138. Where has this figure comes from? Also, data in range B2:H7 looks like data which is already aggregated via formulas (this does not look like the base data). Share the base data (which can be pasted in an Excel workbook) from where this table was built.
Thank you again for prompt response and yes, the fields are aggregated formulars based off the attached spreadsheet of the tables:
PMOData - PMO being unquie identifier
KEYTECH - Individual values from PMOData
REGION - Individual AREA from PMOData mapped to REGIONS
FY15 - Financial spend for FY15 with seperate tables for FY16, FY17, FY18 & FY19 - mapped by PMO identifier
PMOHrs - All hours booked against PMO from FY15 throu FY19
Cnt PMO = COUNTROWS(PMOData[PMO]
LTD $Spend = FY15[FY15 $] + FY16[FY16 $] + FY17[FY17 $] + FY18[FY18 $] + FY19[FY19 $]
Tlt Booked Hrs = SUM(PMOHrs[Hrs Worked]
Avg PMO LTD$ = [LTD $Spend] / [Cnt PMO]
Avg PMO LTDHrss = [CAL Tlt Booked Hrs] / [Cnt PMO]
And the number of 17,138 in the spreadsheet is the Cnt PMO total
https://www.dropbox.com/s/izslnqbk6rc7oqj/Tables.xlsx?dl=0
Greatly appreciated all your ongoing assistance - it really is invaluable
