# Thread: PV Panels Sheet problem

1. ## 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),"")

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

2. 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. 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. If you just want the rightmost number from B21 to M21, you can use:
=HLOOKUP(9.9999E+307,B21:M21,1)

Steve

5. In Excel 2003, you can spot this: Tools . . . Options . . . View tab . . . uncheck Zero values

6. 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. Originally Posted by sdckapr
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. 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. 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
•