+ Reply to Thread
Results 1 to 9 of 9

How to show if the figures have gone up or down

Hybrid View

  1. 10-09-2008, 10:24 AM #1
    akhlaq768
    akhlaq768 is offline
    Forum Contributor
    Join Date
    10-04-2007
    Posts
    118

    How to show if the figures have gone up or down

    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
    Attached Files Attached Files
    Last edited by akhlaq768; 10-14-2008 at 06:41 AM.
    Reply With QuoteRegister To Reply
  2. 10-09-2008, 04:49 PM #2
    19fortysix
    19fortysix is offline
    Registered User
    Join Date
    09-01-2008
    Location
    Houston, TX
    Posts
    70
    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......
    Reply With QuoteRegister To Reply
  3. 10-09-2008, 09:39 PM #3
    sglife
    sglife is offline
    Valued Forum Contributor
    Join Date
    08-26-2008
    Location
    singapore
    Posts
    626
    19fortysix,
    You are great, but why not use the arror symbol
    
=IF(G7='Admitted Patients WE 20080921'!G6,"→",IF(G7<'Admitted Patients WE 20080921'!G6,"↓","↑"))
    I need your support to add reputations if my solution works.

    Reply With QuoteRegister To Reply
  4. 10-10-2008, 07:01 AM #4
    akhlaq768
    akhlaq768 is offline
    Forum Contributor
    Join Date
    10-04-2007
    Posts
    118
    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...


    Quote Originally Posted by sglife View Post
    19fortysix,
    You are great, but why not use the arror symbol
    
=IF(G7='Admitted Patients WE 20080921'!G6,"→",IF(G7<'Admitted Patients WE 20080921'!G6,"↓","↑"))
    Reply With QuoteRegister To Reply
  5. 10-10-2008, 07:08 AM #5
    19fortysix
    19fortysix is offline
    Registered User
    Join Date
    09-01-2008
    Location
    Houston, TX
    Posts
    70
    Because that was the way I learned to do it but the arrows are probably better.....thanks
    Reply With QuoteRegister To Reply
  6. 10-13-2008, 09:33 AM #6
    akhlaq768
    akhlaq768 is offline
    Forum Contributor
    Join Date
    10-04-2007
    Posts
    118
    attached is the dataset inc the formula, but i can't get it to work properly
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-13-2008, 11:13 AM #7
    EdMac
    EdMac is offline
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    You have some errors in your sheet labelling and table labelling in the formula.

    try this

    =IF(ISERROR(IF(VLOOKUP(A8,'Admitted Patients WE 20080921'!$A$1:$G$100,7,FALSE)>G8,"h",IF(VLOOKUP(A8,'Admitted Patients WE 20080921'!$A$1:$G$100,7,FALSE)<G8,"I",IF(VLOOKUP(A8,'Admitted Patients WE 20080921'!$A$1:$G$100,7,FALSE)=G8,"N","")))),"",IF(VLOOKUP(A8,'Admitted Patients WE 20080921'!$A$1:$G$100,7,FALSE)>G8,"h",IF(VLOOKUP(A8,'Admitted Patients WE 20080921'!$A$1:$G$100,7,FALSE)<G8,"I",IF(VLOOKUP(A8,'Admitted Patients WE 20080921'!$A$1:$G$100,7,FALSE)=G8,"N",""))))
    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.
    Last edited by EdMac; 10-13-2008 at 11:32 AM.
    Ed
    _____________________________
    Always learning, but never enough!
    _____________________________
    Reply With QuoteRegister To Reply
  8. 10-13-2008, 09:28 AM #8
    akhlaq768
    akhlaq768 is offline
    Forum Contributor
    Join Date
    10-04-2007
    Posts
    118
    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",""))))
    Reply With QuoteRegister To Reply
  9. 10-14-2008, 06:42 AM #9
    akhlaq768
    akhlaq768 is offline
    Forum Contributor
    Join Date
    10-04-2007
    Posts
    118
    thanks all for your help, once again :-)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to format cells to show only significant figures entered?
    By contextions in forum Excel General
    Replies: 6
    Last Post: 09-26-2008, 11:12 PM
  2. Show Data Lables show Amount & Persentage at the same time
    By ComcoDG in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-29-2008, 04:54 AM
  3. replacing '0' with a blank cell; working out %'s as and when figures are entered.
    By pippam in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-25-2008, 07:28 AM
  4. How to: show taks pane, print last row in cell, change gridlines, show hiden pictures
    By the-trooper in forum Excel General
    Replies: 1
    Last Post: 04-11-2008, 02:02 AM
  5. Assignment overDUE PLEASE PLEASE HELP!!
    By undergrad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-29-2006, 12:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1