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]

 

[AlanY]

instead of the -600% in your example, what are you expecting to get?

 

[halesowenmum]

Well in row 3, 4 is 80% of 5, and since the one that they did was within the last 12 months, that would give them an adjusted completion % of 20% (wouldn't it?!?). As I say, maths calculations are NOT my strong point (apologies, blame it on the teaching methods of the 1970s!). But if there was a figure in column H (which there isn't in this example of course), then the adjusted % would be even lower.

 

[AlanY]

this is what I'd come up with in Column I

=(G3-H3)/D3

 

[halesowenmum]

Thank you AlanY and that works absolutely brill, except for teams that haven't completed any observations in any year - then I get a #DIV/0. Some of the blighters are zero's across the board.

I'm guessing I need to use ISERROR or IFERROR but I'm hopeless with structuring the syntax.

 

[AlanY]

you can trap the error with this

=iferror((G3-H3)/D3,"")

 

[halesowenmum]

Brilliant! I've tweaked it v slightly so that any rows where there's zero's across the board, instead of it leaving a blank with "", I've changed that to "0%" so it shows they've done nothing. Thank you AlanY, I couldn't have done this without you. That's going into the vault of knowledge for future use!

 

[AlanY]

you're welcome
glad that help

 

[halesowenmum]

Sorry, back again like a bad penny due to conditional formatting problems.

I want to show with colour the teams who are failing and those who are on track. I had picked the three colour range that goes green up to red as it seemed pretty appropriate for my needs.

This works great for all the rows where there is a result that's 0.

[TABLE="width: 336"]
<tbody>[TR]
[TD]
9
[/TD]
[TD]D9
12
[/TD]
[TD]F9
1[/TD]
[TD]G9
11[/TD]
[TD]H9
11[/TD]
[TD]I9
0%[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]

But these 0%'s show green - but they should be red because they've failed to do anything.

I'm looking into the CF though and whilst the first cell refers to $I$4 which is the first row of data, the others that have been copied have gone off and referred themselves to a variety of different $erised ranges rather than the CF just referring to the row to which is has been placed on. I have to say, I find this idiocy of how CF pastes and then goes off and makes its mind up about which cells to reference, totally baffling. I fear I may need to do a macro or something from other stuff I've read but I could do with some help from anyone who can, that will spell out how I'd do this for this particular issue.

Many thanks indeed.

 

[AlanY]

what you can try is to clear all the CFx except 1, then goto CF > manage rules > pick that Rule and applies to the range required to see if that solved your problem.

see attached example
https://drive.google.com/file/d/0B1h-Y2fdTl08YlM4OHUzSWJuX0k/view?usp=sharing