I am trying to find how many weeks are above the budgeted target %. I have tried the following formula in the last column but it returns 0
=COUNTIF(F2:BE2,">=BG2") answer should be 4
Any help greatly appreciated
Thanks, Paul
| W27 | W28 | W29 | W30 | W31 | W32 | W33 | W34 | W35 | W36 | W37 | W38 | W39 | W40 | W41 | W42 | W43 | W44 | W45 | W46 | W47 | W48 | W49 | W50 | W51 | W52 | W53 | W54 | W55 | W56 | W57 | W58 | W59 | W60 | W61 | W62 | W63 | W64 | W65 | W66 | W67 | W68 | W69 | W70 | W71 | W72 | W73 | W74 | W75 | W76 | W77 | W78 | Budgeted Occupancy | Actual Occupancy | Variance | No of Weeks above Budget | |
| 61.0% | 51.6% | 54.4% | 66.6% | 63.4% | 68.2% | 70.9% | 57.9% | 49.5% | 52.1% | 50.7% | 35.0% | 17.5% | 43.0% | 51.6% | 51.5% | 48.9% | 54.0% | 65.3% | 57.6% | 52.8% | 52.4% | 57.4% | 56.8% | 45.3% | 47.3% | 46.2% | 51.9% | 39.2% | 50.9% | 56.8% | 58.7% | 66.8% | 56.6% | 69.7% | 60.6% | 58.6% | 57.6% | 64.2% | 49.5% | 52.3% | 59.9% | 62.1% | 73.2% | 67.3% | 75.8% | 61.1% | 58.2% | 71.6% | 63.6% | 74.0% | 70.1% | 71% | 59% | -12% | 0 |
=COUNTIF(F2:BE2,">="&BG2)
Thanks Clark,
I can see I was nearly there, so I can understand for the future what does adding the & before BG2 do?
Thanks
Paul
Hi Paul
& connected the mathematical operators ">=" and the criteria.
If the value in cell BG2 is 100, the two formula calculated as follows:
1)=COUNTIF(F2:BE2,">="&BG2) equals to =COUNTIF(F2:BE2,">=100")
">="&BG2---------->">="&100---------->">=100", Excel identifies BG2 as a reference to a cell, and will return the value of the cell first.
2)=COUNTIF(F2:BE2,">=BG2")
">=BG2"---------->">=BG2", "BG2" was identifies as usual text, generally, contents inside double quotes "" will be identified as text and BG2 here will not return the value of the cell BG2.
Clark
Thank you Clark, I now totally understand where I was going wrong
Regards
Paul
