How to create a %, adjusted actual against target calculating out non-conforming data

[halesowenmum]

Hi all

Can I explain what I'd like to be able to do with the following data please? In I3 the formula is =(F3/(1-D3))-G3-F3 but this is not what I need and as you can see, it's not working anyway. I need a formula that will:

1. not produce any #DIV/0 errors
2. In column I it will give the result which shows the 'real adjusted' actual % of observations which were carried out. This would be calculated from the data in columns D F and G but also taking into account where there is data also in column H. For example, I have rows where D might = 10, F = 0, G = 10, but in column H it's a 5 meaning that in actual fact because observations from 1 yr ago are no longer valid, the Western District would actually have only achieved a score of 50%. There could be any variety of variation in these figures in those columns really (although mostly, they're actually zeros across the board in some of the teams that are further down on my list).

Can anyone help with a formula that would cope with all of this??


[B]Excel 2007[/B][TABLE]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]D[/TH]
[TH]F[/TH]
[TH]G
[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]2[/TD]
[TD]Division[/TD]
[TD]Team[/TD]
[TD="align: center"]No. of Staff in this Team[/TD]
[TD="align: center"]No. of Missing Obs[/TD]
[TD="align: center"]No. of Obs that were done[/TD]
[TD="align: center"]No. of Obs Older than 1 Year[/TD]
[TD="align: center"]Adjusted Completion Score %[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Teaching[/TD]
[TD]Western District[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-600%[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Sheet1
[/B][/COLOR][/CENTER]

 

[halesowenmum]

I changed the single cell reference in the edit CF rules box so that it extended to all the data in that column. I then pasted formats down. It still does the errors I described in the previous message so I'm afraid that didn't work. If you meant for me to do something else do let me know as I wasn't quite sure exactly what you meant for me to do.

Then I changed it so it was referring to $I4 and pasted format it down the column - same issue.

So don't really know what to do next.

 

[AlanY]

I changed the single cell reference in the edit CF rules box so that it extended to all the data in that column. I then pasted formats down. It still does the errors I described in the previous message so I'm afraid that didn't work. If you meant for me to do something else do let me know as I wasn't quite sure exactly what you meant for me to do.

Then I changed it so it was referring to $I4 and pasted format it down the column - same issue.

So don't really know what to do next.

are you ok to post a sample of your data? dropbox may be?

 

[AlanY]

save your copy before you try this

1) Goto CF Clear rules from entire sheet
2) left click Cell I3
3) Goto CF > New Rule > Format only cells that contain (second down) > Format only cells with: Cell Value (1st box) Equal to (2nd box) 0 (last box)
4) choose format required
5) OK
6) go back to CF > manage rules, pick the only rule there and applies to, =$I:$I

 

[halesowenmum]

Hi. I've tried this and it successfully addresses one of the issues which is the CF 'bloat' so that's a very good start.

But this only gives me one colour (red in this case) for anything that's a zero... I need a red amber green setup based on say green for teams who'd achieved 90% or more, amber for those who'd got between 70 and 89%, and red for the rest.

There is still one issue with the zero's though based on this latest CF formatting. Where it's 2 2 0 giving a 0% result, then the CF works. Where there's a 0 0 0 then the CF isn't getting applied and there's no red highlighting to these cells. Excel seems to be treating these differently when it comes to applying the CF.

Any ideas anyone??



I can't really post the table due to data protection.

 

[AlanY]

Hi. I've tried this and it successfully addresses one of the issues which is the CF 'bloat' so that's a very good start.

But this only gives me one colour (red in this case) for anything that's a zero... I need a red amber green setup based on say green for teams who'd achieved 90% or more, amber for those who'd got between 70 and 89%, and red for the rest.

There is still one issue with the zero's though based on this latest CF formatting. Where it's 2 2 0 giving a 0% result, then the CF works. Where there's a 0 0 0 then the CF isn't getting applied and there's no red highlighting to these cells. Excel seems to be treating these differently when it comes to applying the CF.

Any ideas anyone??



I can't really post the table due to data protection.

you can repeat the procedures by adding more CFs for the same cell and applies to column as before.

for green, that will be value "greater than or equal to" .9
for amber, "between" .7 and .89
for red, "less than" .7

as for the 0% that not working, did you changed the "" to 0%? if so, change the CF to "specific text" and "containing" "0%" should work

 

[halesowenmum]

Having now created three rules as per your instructions and making sure each one was $I:$I, it's working beautifully, aside again from the ones where there's a totally 0 range of values resulting in a 0 adjusted %.

However, if I change the existing red one to specific text "0%" won't that only capture the zero ones - what about the 1% to 69% - wouldn't those results then be left out???

I did try adding in a fourth red rule that had those criteria, but it again didn't make any difference - the 0 results that resulted from columns with all 0s in them, still didn't turn red (in fact they went green).

 

[AlanY]

this CF > "less than" .7 should capture all <70% and "0%".

The reason for the 0s shown green is probably the nurmerial 0s are formatted as text. if you click on that cell, is that shown '0 instread of just 0?
for testing, delete the cell and input just 0 should be conditional formatted as red.

 

[halesowenmum]

Yes, those zero's are coming out as text - still not clear how I deal with that within the CF - or does the formulae behind the calculations need to change???

 

[AlanY]

try this formula in Col I instead

=IFERROR((G3-H3)/D3,0)

then select Col I and Goto > Home ribbon > click the % icon to format the column as percent

that should bring all cells under 70% red, hopefully.

 

[halesowenmum]

I'll have a got at this on Monday. Thank you!