Usage calculation

[nickcooluz]

Hi,

I am not a pro at excel and I was looking for some help/advice. I have a table in my sql server which gets updated with the usage (of tables) figures every week. The data gets copied to the excel file.

\1

I was wondering if there is a way to return the increase in usage percentage of all the columns for every week compared to the last week.

Thank you in advance

table usage.xlsx

[JohnTopley]

What is the calculation for usage %?

[nickcooluz]

Asumingly it will just be dividing prior by current to calculate by how much percentage it has increased by

[FDibbins]

how would you do this manually?
which cells would you use?

[nickcooluz]

I would use C2, C7 and C12 to calculate the increase in usage by date for table tblEvents and same for rest of the tables.

[JohnTopley]

General formula used (and it assumes a constant 7 days between readings: hence the "-7" in the formula)

=IFERROR((INDEX($C$2:$G$17,MATCH($B7&$H7,$B$2:$B$17&$H$2:$H$17,0),1)-INDEX($C$2:$G$17,MATCH($B7&$H7-7,$B$2:$B$17&$H$2:$H$17,0),1))/INDEX($C$2:$G$17,MATCH($B7&$H7-7,$B$2:$B$17&$H$2:$H$17,0),1),"")

Enter with Ctrl+Shift+Enter


NOTE: I had to change dates to dates only (rather than date / time).

[nickcooluz]

Thank you for the formula, if I copy and paste more data below the existing data. Will i be able to drag down the function to calculate weekly figures?

[JohnTopley]

Yes ... you should be able to do that but change date/time to date only.

If your data is date/time you can convert to date only using =INT(H2). I put this formula into column I and copied down, then COPY/PASTE SPECIAL VALUES back to H. You can copy the same dates into J.

[nickcooluz]

Thank you so much for your help, really appreciate it.

[JohnTopley]

IF you are happy with the solution could you please mark thread as SOLVED ("Thread Tools" at top of first post).