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

 

[Dummy Excel]

Anthony47
Historical!$I:$I = the week numbers that the incident happened
yes, i always get 100%, which is better than the percentage i was getting before
so in Historical!$I:$I for wk 42 there are 9 incidents

Joe4
expected result is 92% in this instance


thanks
Sam

 

[Joe4]

So, what number are you expecting your COUNTIFS to return then?
Something like 10? (so we would have something like:
=(119-10)/119 = 92%

So, we probably want to focus more on the COUNTIFS part of the formula.
Does this formula return 10?

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

If not, what if you hard code the last argument, i.e.

=COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,[COLOR=#ff0000]42[/COLOR])

(assuming week number 42 is what you want).
Does that return 10? If that returns 10, then there is probably an issue with the last part of the formula.
If it does NOT return 10, then there most likely is a data issue, or you are misinterpretting the data, and it should NOT be returning 92%.

By the way, if you are able to upload a copy of your workbook to a file share siting, and provide a link to it, it might go much better, as we can see the exact data you are working with.

 

[Anthony47]

Don't miss Joe's message, above

I think that if you share a sample workbook it would go much much much better...

 

[Dummy Excel]

its HTML maker isn't it? (new laptop)

 

[Anthony47]

its HTML maker isn't it? (new laptop)

I suggest that you upload your file to a filesharing server, for example filedropper.com, or dropbox.com
Then you publish the share link that point to the file

Bye

 

[Dummy Excel]

morning,
sorry its taken so long

http://www.filedropper.com/mrexcel_1
[/code]

so in the worksheet "table", im trying to build the matrix for 52wks of availability.
it will be a rolling data and therefore will have data from the previous year

i still keep coming back to my formula is calculating incorrect as its trying to calc current week number vs last week number ie weeknum formula converting the week number from a date to a number

any help is greatly appreciated
thanks
Sam

 

[Anthony47]

Ok, now we have a sample of your data, and we understand you need to fill the table in sheet Table.

Could you tell us what you wish to calculate in cells (for example) Q25? I mean the logical description of the calculation to be made, and then, based on this logic, which results you wish to get in Q25 and R25, and which are the figures that generate these results?

Bye

 

[Joe4]

I am not sure I am totally clear on exactly what you are after. If you could explain your expected results based on your uploaded file, maybe that will make more sense.

But to go back to something here, what you said to Anthony:

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

You would need two complete COUNTIF formulas, and subtract them from one another.

For example, this week's count would be:

=COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,WEEKNUM(TODAY()))

Last week's would be:

=COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,WEEKNUM(TODAY())[COLOR=#ff0000]-1[/COLOR])

To get the specific week number in P27, it would be:

=COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,P27)