Log In
Register
Home
July 11, 2017
Offline
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 |
See AnswerNovember 19, 2017
Offline
=COUNTIF(F2:BE2,">="&BG2)
Answers Post
July 11, 2017
Offline
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
November 19, 2017
Offline
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
1 Guest(s)
