Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Hiding zeroes in Office 2002
Hi
My mate uses Excel 2002. I've talked him through setting up a few formulae, one of which is like this: =IF(A2="",0,VLOOKUP(A2,F:H,2,FALSE)) When A2 is empty, this returns a zero - which, he says, cannot be hidden with conditional formatting or Tools|Options|View / Zero Values. He has another formula to multiply this value by a quantity, so he can't return a "" - it needs to be a number. I use Office 2000 - and don't have this problem!! Both of the above hide my zeroes. If anyone can shed any light on this I'd appreciate it! Cheers. -- Andy. |
#2
|
|||
|
|||
Sounds as though he's returning "0" instead of 0. After making sure that the
formula starts out =IF(A2="",0, and not =IF(A2="","0", then try custom formatting where there is nothing after the second semicolon. Something like: #;(#); "Andy B" wrote: Hi My mate uses Excel 2002. I've talked him through setting up a few formulae, one of which is like this: =IF(A2="",0,VLOOKUP(A2,F:H,2,FALSE)) When A2 is empty, this returns a zero - which, he says, cannot be hidden with conditional formatting or Tools|Options|View / Zero Values. He has another formula to multiply this value by a quantity, so he can't return a "" - it needs to be a number. I use Office 2000 - and don't have this problem!! Both of the above hide my zeroes. If anyone can shed any light on this I'd appreciate it! Cheers. -- Andy. |
#3
|
|||
|
|||
Are you sure he knows to UNcheck the Zero Values box?
-- Vasant <Andy B wrote in message ... Hi My mate uses Excel 2002. I've talked him through setting up a few formulae, one of which is like this: =IF(A2="",0,VLOOKUP(A2,F:H,2,FALSE)) When A2 is empty, this returns a zero - which, he says, cannot be hidden with conditional formatting or Tools|Options|View / Zero Values. He has another formula to multiply this value by a quantity, so he can't return a "" - it needs to be a number. I use Office 2000 - and don't have this problem!! Both of the above hide my zeroes. If anyone can shed any light on this I'd appreciate it! Cheers. -- Andy. |
#4
|
|||
|
|||
Brilliant!! The initial formula was to return "" and when I told him to
replace the "" with 0 he didn't!! He just stuck the 0 in between the "s. Thanks a lot. -- Andy. "Duke Carey" wrote in message ... Sounds as though he's returning "0" instead of 0. After making sure that the formula starts out =IF(A2="",0, and not =IF(A2="","0", then try custom formatting where there is nothing after the second semicolon. Something like: #;(#); "Andy B" wrote: Hi My mate uses Excel 2002. I've talked him through setting up a few formulae, one of which is like this: =IF(A2="",0,VLOOKUP(A2,F:H,2,FALSE)) When A2 is empty, this returns a zero - which, he says, cannot be hidden with conditional formatting or Tools|Options|View / Zero Values. He has another formula to multiply this value by a quantity, so he can't return a "" - it needs to be a number. I use Office 2000 - and don't have this problem!! Both of the above hide my zeroes. If anyone can shed any light on this I'd appreciate it! Cheers. -- Andy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I setup to tab between form fields in Office Excel 2002? | Excel Discussion (Misc queries) | |||
how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) | |||
Microsoft Office 2002 Excel sheets | Excel Discussion (Misc queries) | |||
update row numbers after different active cells in macros followi. | Excel Discussion (Misc queries) | |||
Reinstall Excel 2002 after removing it during upgrade to Office 20 | Excel Discussion (Misc queries) |