Hello,
I'm converting English to Metric from one sheet to another, but can't get it to show blank in the metric cell instead of 0. Sheet1 is English and sheet2 is Metric.
My formula is: =CONVERT(English!H7,"mm","in")
The formula works, but I can't figure out how to have the cell show blank instead of zero.
Thank you
JG23
You can format the cell as Custom: 0;-0;;@
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I have actually tried the 0;-0;;@ in each individual sheet and both together to no avail.
Any other suggestions?
Thank you
If the actual result is 0, then that hides 0's.. are the actual results a decimal value showing up as 0? If so, maybe if you round the Convert() formula result first to 0 decimals... then format as 0;-0;;@
you can build something into your formula like:
=IF(=CONVERT(English!H7,"mm","in")=0,"",CONVERT(English!H7,"mm","in"))
where, again, if you want to exclude decimals, you will need to ROUND or something like that...
These are measurements down to the tenths. If I leave the cell empty in the first sheet, on the second sheet (the one with the convert formula) shows a zero. I want them to be blank other wise I will have 200 cells with zeros in them making it hard to read the rest of the information.
You will need to adjust the formula then.. for example:
=IF(ROUND(CONVERT(K1,"mm","in"),0)=0,"",CONVERT(K1,"mm","in"))
NBVC, Thank you! Your =IF(CONVERT... statement worked after I removed the = sign in front of CONVERT.
Thanks again, this was a big help!
Didn't you still have to round?
Just noticed the rounding issue. Some cells are rounding to 3 decimal places and others 4 or more. I need them to all round to 3 decimal places.
Perhaps?
=IF(ROUND(CONVERT(English!H7,"mm","in"),3)=0,"",ROUND(CONVERT(English!H7,"mm","in"),3)
Works Great! Thanks again NBVC!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks