Date calculating as a number in formula

[Dummy Excel]

Hi All,
i have a formula which im trying to calculate a percentage based on historical data by week
Formula:

=(Networks!$C$62-COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,WEEKNUM((TODAY())-P27)))/Networks!$C$62

I believe the formula is correct, although when i "evaluate" the formula it changes weeknum to a number (43761) which i understand why, although thats where the problem is.
"WEEKNUM((TODAY())-P27)" becomes (43761-42) as P27=WEEKNUM(TODAY())-1) to get the previous week number


So the formula evaluates to 3719 which equals to wk 37 and not wk 42

HELP!
thanks
Sam

 

[Joe4]

If you are doing what I think you are, I believe you have your parentheses in the wrong spot. You are subtracting 42 from the date, then taking the Week Number of that, when I think what you want to do is take the Week Number of the current date, and then subtract 42 from that week number.

So, your formula should look like this:
WEEKNUM(TODAY())-P27
or this:
(WEEKNUM(TODAY())-P27)

 

[Anthony47]

In short, you are asking how to do a calculation that your formula doesn't return, without saying what you want to calculate and without showing how the data are organized...
The only thing I can guess is that you should use WEEKNUM(TODAY())-P27) rather than WEEKNUM((TODAY())-P27))

Or you should explain what, that portion of the formula, should return

Bye

 

[Dummy Excel]

In short, you are asking how to do a calculation that your formula doesn't return, without saying what you want to calculate and without showing how the data are organized...
The only thing I can guess is that you should use WEEKNUM(TODAY())-P27) rather than WEEKNUM((TODAY())-P27))

Or you should explain what, that portion of the formula, should return

Bye

Hi Anthony, sorry you are right.
that part of the formula is trying to basically workout the "current week number" minus "last weeks week number".
im trying to build a table with 52 weeks of data to provide a weekly percentage

does that make sense?

 

[Joe4]

Did you see my reply/try my suggestion?
I think it should solve your issue, and I explained what you did wrong.

 

[Dummy Excel]

Did you see my reply/try my suggestion?
I think it should solve your issue, and I explained what you did wrong.

Hi Joe4
yes sorry, got side tracked and haven't had chance to reply although yes thats correct
im getting 11900% as the result which obviously isn't right!
i think its got to do with the parentheses as well although couldn't work it out

 

[Joe4]

I haven't analyzed your formula, just the one part that you mentioned that you were having issues with.
Let's break the formula down into parts, and see what each part returns:

=Networks!$C$62
=COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,WEEKNUM(TODAY())-P27)

What do each of those formulas return?

What is the value you expect to get from your data?

 

[Dummy Excel]

Networks!$C$62 = 119 (total number of entries)
Historical!$A:$A = North (our territory in the raw data)
Q$16 = North (territory in the table)
Historical!$I:$I = count of data for North for availability
WEEKNUM(TODAY()) = current week ie 43
P27 = 42

 

[Anthony47]

that part of the formula is trying to basically workout the "current week number" minus "last weeks week number"

In this case you should use WEEKNUM(TODAY())-P27), ie:

=(Networks!$C$62-COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,WEEKNUM(TODAY())-P27))/Networks!$C$62

But I don't know what is in Historical!$I:$I, cannot say if the formula is logically correct or not
Also I seem that "current week number" minus "last weeks week number" will always be "1", to add uncertainty to the formula adopted

Bye

 

[Joe4]

You haven't told us what your expected value is.
What percentage do you expect to see in this example?