Date calculating as a number in formula

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
1,004
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
Hi All,
i have a formula which im trying to calculate a percentage based on historical data by week
Formula:
Code:
=(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
 
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
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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?
Code:
=COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,WEEKNUM(TODAY())-P27)
If not, what if you hard code the last argument, i.e.
Code:
=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.
 
Upvote 0
Don't miss Joe's message, above

I think that if you share a sample workbook it would go much much much better...
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Code:
=COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,WEEKNUM(TODAY()))

Last week's would be:
Code:
=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:
Code:
=COUNTIFS(Historical!$A:$A,Q$16,Historical!$I:$I,P27)
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top