Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts

    PV Panels Sheet problem

    I've not come to these pages before so am not sure if I can attach a whole spreadsheet (it is a small one !). so I'm attaching a screen shot and the formulae that is causing the problem.

    The problem is the '#VALUE' in cell N21.

    This cell shows the Deviation between Actual and Target on a weekly (actually 1/4 monthly) basis and the formula for it is this, to show the last numerical value in Row 21.

    =IF(HLOOKUP(MONTH(TODAY()),B2:M21,20,FALSE), HLOOKUP(MONTH(TODAY()),B2:M21,20,FALSE), HLOOKUP(MONTH(TODAY()-8),B2:M21,20,FALSE))

    This works fine even though there might well be a better way of doing it - what has upset the apple cart is that I decided to change all the cells that were showing '0' and replace that with "" to give a blank cell, so E21 now reads

    =IF((E6>0),(D21+E20),"")

    instead of =IF((E6>0),(D21+E20),0)

    and HLOOKUP won't cope with that!

    I'm sure there is better option than the HLOOK one, but I don't have enough Excel knowledge to find it!

    It's Excel2003 by the way.

    Many thanks for any help

    Rob
    Attached Images Attached Images

  2. #2
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    240
    Thanks
    3
    Thanked 24 Times in 24 Posts
    Instead of changing the formula, why not just change the worksheet to not display zero values?

    In Excel 2010 you select File, Options, Advanced. Scroll down to the "Display options for this worksheet" and uncheck the box "Show a zero in cells that have a zero value"

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Ahh - there's the catch - I'm on Excel 2003 (which you didn't spot!). That's potentially a nice facility until you get to a cell which should show '0'.

    What I have is a row of monthly data and I want to extract the last one of the row into another cell (Totals)

    Rob

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you just want the rightmost number from B21 to M21, you can use:
    =HLOOKUP(9.9999E+307,B21:M21,1)

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    In Excel 2003, you can spot this: Tools . . . Options . . . View tab . . . uncheck Zero values

  6. #6
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    240
    Thanks
    3
    Thanked 24 Times in 24 Posts
    I tried this, works well, borrowed from TechRepublic at; http://www.techrepublic.com/blog/mso...cel-sheet/8268

    Numeric format

    If you want to hide specific zero values, but not all, you can use a numeric format instead:
    1.Select the cells that contain the zero values that you want to hide.
    2.Click the Home tab and click the Number group’s dialog launcher (the small arrow in the bottom-right corner. In Excel 2003, choose Cells from the Format menu.
    3.Click the Number tab (if necessary).
    4.Choose Custom from the Category list.
    5.Enter 0;-0;;@ in the Type field.
    6.Click OK

  7. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    If you just want the rightmost number from B21 to M21, you can use:
    =HLOOKUP(9.9999E+307,B21:M21,1)

    Steve
    Steve - very many thanks indeed.

    That's exactly what I want but I'm puzzled how that does it! The BIG number is the Lookup Value, then the Range, but the '1' is the "A row_index_num of 1 returns the first row value in table_array", and I would have thought that that is B21 - why does it give what I would have thought is the last row value?

    Again thanks
    Rob

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Rob

    The BIG number is the largest number you can have in Excel, so the HLookup keeps looking to the end of your specified row, thus returning the very last number found. If you were sure that all your numbers in the row were always less than say, a million, you could ask Hlookup to 'look for' 5 million, and so this would find your last number (because there aren't any more to check).
    The 1 is simply because you are returning the value from the first row in your search range (which happens to be row21 in your case)

    zeddy

  9. #9
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Many thanks, Zeddy.

    I'd been given my original HLOOKUP formula by a spreadsheet knowledgeable friend, but I suspect his knowledge isn't as good as yours, and that is itself considerably better than MS's Excel Help file, which certainly is that clear about how the function works.

    I adjusted the BIG number down to see what effect it had, but did realise it's significance.

    Again many thanks

    Rob

Posting Permissions

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