#VALUE! and hiding cells...

[bensmith007]

Hello all you lovely people that help us noobs understand a little excel!

This is a question about a very simple spreadsheet recording the amount of fat/protein/carbs etc. I ate over a two day period. I can't seem to apply advice for similar problems on =ISNUMBER and similar. Here goes...

I have two tables; table 1 is an amount per 100g of carbs, protein, fat, vitamins etc for given foods I ate each day (directly input as absolute values). Table 2 uses the data from table 1 to calculate what I actually ate as a ratio of the 100g amounts. Some of the data given is not a number i.e. 'trace' or "N/A" for some vitamins, and when used in the formula gives #VALUE! when the formula is applied. This isn't a problem in itself until I want to do a sum for each column which might have a few of these non-numerical values. So I know how to get round i.e. control-click select numerical values only, but how do I fix it with a logic statement or similar? FYI, basic formula is =($C5/100)*D5 where C5 is nutrient or vitamin amount per 100g and D5 is the amount in grams I ate of the nutrient.

Also, I want to hide the cells which just have the raw data (which is currently side by side with the second table on the same sheet) to just show the relevant formulae results. How do I go about that?

Thanks so much in advance for your help!!

Ben

I have created a little spreadsheet for a food diary I've kept for my nutrition class.

[NBVC]

For the formula that is giving you the #VALUE error, you can enclose it in an IFERROR() to return a blank or 0 instead.

e.g.

=IFERROR(your_formula,"")

or

IFERROR(your_formula,0)

[DGagnon]

you can use the formula

=IFERROR(($C5/100)*D5,0)

this would give you a 0 when ever there was an error, as for hiding the raw data, you have 2 options. you can cut it out and move it to another sheet, or you could hightlight the columns, right click, and hide them.

hope this helps.

[bensmith007]

Nice one! thanks for that, always easy when an expert explains!!