After getting the cumulative comparison up to date of all numbers, I was trying to figure out how to compare the same weekdays of the month to last year but trying to calculate it up to the last posted day with the same number of days.
For example, if I have more Sundays recorded to date for one year, I would only compare the two years with the earliest Sundays of the month up to the same number of Sundays for each year.
Example of numbers is attached.
Okay, there probably is an easier way but.
In H6, dragged down
Formula:
=IF(D6>0,COUNTIF($A$6:$A6,A6),"")
In I6, dragged down
Formula:=IF(D6>0,COUNTIF($B$6:$B6,B6),"")
and, (here's the kicker) in F41 dragged down
Formula:=IF(COUNTIFS($A$6:$A$36,$E41,$D$6:$D$36,">0")=COUNTIFS($B$6:$B$36,$E41,$D$6:$D$36,">0"),(1-SUMIFS($D$6:$D$36,$A$6:$A$36,$E41,$D$6:$D$36,">0")/SUMIFS($E$6:$E$36,$A$6:$A$36,$E41,$D$6:$D$36,">0")), IF(COUNTIFS($A$6:$A$36,$E41,$D$6:$D$36,">0")>COUNTIFS($B$6:$B$36,$E41,$D$6:$D$36,">0"),(1-SUMIFS($D$6:$D$36,$H$6:$H$36, "<="& COUNTIFS($B$6:$B$36,$E41,$D$6:$D$36,">0"), $A$6:$A$36,$E41,$D$6:$D$36,">0")/SUMIFS($E$6:$E$36,$A$6:$A$36,$E41,$D$6:$D$36,">0")), (1-SUMIFS($D$6:$D$36, $A$6:$A$36,$E41,$D$6:$D$36,">0")/SUMIFS($E$6:$E$36,$A$6:$A$36,$E41,$I$6:$I$36, "<="& COUNTIFS($B$6:$B$36,$E41,$D$6:$D$36,">0"),$D$6:$D$36,">0"))))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Wow! I see you are an expert! Have tried to decipher the general logic of the last large formula. Looks like several conditional statements within other conditional statements? Can you explain the logic of the final long formula?
Also I tried it and seems to work, but when I made monday and tuesday(the 27th and 28th of 2012) zero to see if the formula worked cumulatively with fewer days, I got a "divide by zero error" on the Monday and Tuesday. Then Monday and tuesday give a result when I make the days zero starting on the 21st.
Will see if I can attach this example with another excel document to this thread. However, do not see how to attach yet.
Am attaching another excel file with a second tab where I adjusted the numbers and got an error message. thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks