How to show if the figures have gone up or down

[akhlaq768]

Im wondering if you guys can help.

I need to indicate if the figures for this week has gone up or down compared to last weeks figures.

So looking at the attached, I need to add a column (I5) to sheet Admitted Patients WE 20081005, so it compares the % treated in <18 wks figure by specialty to previous weeks figures on sheet Admitted Patients WE 20080921.

Ideally I would love an arrow either point up if the figures has increased or down if they have reduced.

I’m open to suggestions.


Thanks in advance

[19fortysix]

Here's one way it could be done. In cell I7 on worksheet "Admitted Patients WE 20081005" enter this formula =IF(G7='Admitted Patients WE 20080921'!G6,"""",IF(G7<'Admitted Patients WE 20080921'!G6,"$","#")) and then format as Wingdings 3. The fomula can then be copied down but you must make sure that the rows on each correspond. In the above formula the comparison is between Row 7 and Row 6 because "Admitted Patients WE 20081005" has NULL in Row 6 but "Admitted Patients WE 20080921" does not.
Hope this helps......

[sglife]

19fortysix,
You are great, but why not use the arror symbol

Please Login or Register  to view this content.

[akhlaq768]

thanks for this, i need to reference the specialty field aswell as the % treated in <18 wks field.

so i need to look at the specialty, match it against the specialty from the previous week and see if the current weeks % treated in <18 wks compares to previous figures...

19fortysix,
You are great, but why not use the arror symbol

Please Login or Register  to view this content.

[19fortysix]

Because that was the way I learned to do it but the arrows are probably better.....thanks

[akhlaq768]

ive added a vlookup... so even if the data isn't in order it will still work... but i cant get it to compile...

=IF(ISERROR(IF(VLOOKUP(A6,Admitted Patients WE '20080921'!$A$1:$A$100,7,FALSE)>G6,"h",IF(VLOOKUP(A6,Admitted Patients WE '20080921'!$A$1:$A$100,7,FALSE)<G6,"I",IF(VLOOKUP(A6,Admitted Patients WE '20080921'!$A$1:$A$100,7,FALSE)=G6,"N","")))),"",IF(VLOOKUP(A6,Admitted Patients WE '20080921'!$A$1:$A$100,7,FALSE)>G6,"h",IF(VLOOKUP(A6,Admitted Patients WE '20080921'!$A$1:$A$100,7,FALSE)<G6,"I",IF(VLOOKUP(A6,Admitted Patients WE '20080921'!$A$1:$A$100,7,FALSE)=G6,"N",""))))

[akhlaq768]

attached is the dataset inc the formula, but i can't get it to work properly

[EdMac]

You have some errors in your sheet labelling and table labelling in the formula.

try this

Please Login or Register  to view this content.

Oh, and you need to change the font from wingdings to an alpha font e.g. ariel if you want letters - leave it as it is if you want arrows.

[akhlaq768]

thanks all for your help, once again :-)