Calculate weekly percentage each week

[bdrod]

HI

I have a table as per attached file and each week the last week's data is added in. So next week we will have the figures for Week 9. I need to calculate a weekly percentage, but I am looking to have this done using a formula.

Currently, weekly percentage refers to Week 8. But when Week 9 data is added in, I need the weekly percentage to be based on week 9 figures.?

can this be done?

Thanks!

[PaulSP8]

If you put this formula in K6

Please Login or Register  to view this content.

It'll adjust itself as you add more columns in for your weeks.

It'll throw an error when you add the new column until you give it some data, if that matters then you can wrap it in an IFERROR

Please Login or Register  to view this content.

Does that work for you?

[bdrod]

Hi Paul
Thanks for your reply. Its giving me a #DIV/0! error - is the formula referring K8 to divide by? K8 is blank so maybe that's why the error?

I need it to divide I6/I8 in Week 8 and then J6/J8 in week 9 and so on.

Thanks!

===================

resolved: Thank you, it worked. I had to change the row reference in my actual sheet.

[PaulSP8]

It'll give you an error until you give it something to divide

I've attached a Workbook with it working for you, and wrapped it in the IFERROR

I assume you'll add a week by inserting a column?

Once you do this then the formula will return "" making it look blank till you plug some figures into it.

Once you've added in the detail for Week 9 the percentage will return.

[PaulSP8]

resolved: Thank you, it worked. I had to change the row reference in my actual sheet.

Ah, cool - No worries - Glad to help

[bdrod]

Hi Paul

I have a question if you don't mind...

I am trying to set this up so there is minimal work each week. So now I have created a pivot to shows Errors / No Errors by week and this pulls though to a Table on the Summary Page.

The table is set up for all 52 weeks as per the attached file. So each time new data is added, pivtos refreshed, the table updates. But in this case, the OFFSET function does not work as the columns between the last week and weekly% will change).

Any ideas how I can calculate this?

Thanks in advance.