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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 01:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com