If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Office 2003 / Office XP Shortcut Bar | Marc Bressman | General Discussions | 6 | June 26th, 2004 08:42 AM |
Product Key for Office XP | P.G.Indiana | Setup, Installing & Configuration | 1 | June 7th, 2004 03:22 AM |
Error #1321 MOS 2003 Setup | Chad Harris | Setup, Installing & Configuration | 1 | June 7th, 2004 12:22 AM |
completely delete office2000 | jason | Setup, Installing & Configuration | 1 | May 28th, 2004 10:08 AM |
Office 2003 and Office 2000/97 coexist on same PC? | John | General Discussions | 3 | May 6th, 2004 12:36 PM |