Normalize data

[Josm95]

Hi,

Is there a way to scale down my data set to a specific amount?

What i'm trying to achieve is this:

Let's say i have a project that takes 132 weeks and in each week a number of hours are spend on that project.

I want to normalize the data to 50 weeks. So i can compare different project with a different length with each other.

Any idea how i can achieve that?

I've attached an example of data.

Thanks in advance.

[Kaper]

Assuming the load during the week is constant, one could calculate the "week's" length ratio as (F2):

=MAX(C:C)/MAX(G:G)

equal 2.64 .
it can be used to select appropriate parts of weeks from left column - for normalized week1 data from weeks 1, 2 and 0,64 of week 3
for normalized week 2 data from 0,36 (rest) of week 3, then whole weeks 4 and 5 and 0.28 of week 6 etc.
I left it in a column-by column easy formulas to calculate whole weeks sum, and parts before first whole week and after last whole week.
The formulas are in yellow cells in attached sheet. In H2 just write 1, and in H3:

=H2+$F$2

then the following formulas in cells and copied down

I2: =SUMIFS(D:D,C:C,">"&H2,C:C,"<"&H3-1)

J2: =(1+TRUNC(H2)-H2)*VLOOKUP(TRUNC(H2),$C$2:$D$133,2,0)

K2: =(H3-TRUNC(H3))*VLOOKUP(TRUNC(H3),$C$2:$D$133,2,0)

L2: =SUM(I2:K2)

final touches: K51 write 0
H51 copy down one more row to H52 (it's used by I51)

PS. The formulas could be nested into one lon monster-formula (well, I've seen longer on this forum) but it would lose readability while formulas above are rather basic and readable :-)


Enjoy!

[Josm95]

Thank you very much Kaper, this is exactly what i need.

Cheers.

---

Best Regards,

Jos

[Josm95]

Hi Kaper,

Just one more question, i tried the formula with another set of data, but the total of the normalized hours doesnt match the total of the original data.
Can't quite figure out why it would not work. Any idea?

Thanks.

I'v attached the file, see the blue marked cells.

Jos

[Kaper]

I can quickly spot where the problem is:
it's not taking into account week 73 hours 8
Which shall be used in normalized week 25 (a representation of standard weeks 71.08 - 74).
so the condition in I2:

=SUMIFS(D:D,C:C,">"&H2,C:C,"<"&H3-1)

is too strong.
Seems that

=SUMIFS(D:D,C:C,">"&H2,C:C,"<="&H3-1)

Test it and observe if it behaves better

[Josm95]

Thanks, that seems to work!