# What is wrong with this Excel formula?

I have a range including a Quantity column (`D`) and a Rate Column (`E`). In cell `F1`, I have the formula `=D3 * E3`, for a sub-total of Rate * Quantity.

Most of my Rate cells are empty, causing most of column `F` to just display, `#VALUE!`.

I thought I'd replace my formula with `=IF(ISNUMBER(E1), E1 * D1, 0)`, hoping that when `E1` is empty, I get a zero for my subtotal.
However, Excel doesn't like that formula, I get a popup saying there is something wrong with my formula, e.g. syntax etc.

It seems quite OK to me, if `E1` is a number, use `E1 * D1`, otherwise use `0`.

Excel complains when I try and enter the formula `=IF(ISNUMBER(E1), E1 * D1, 0)`. Why does it do so?

• If you have formula like `E1*D1` and since most of cells are empty in `Column D` then Excel must return `0` instead of `#Value` error and if you want to replace `0` with `blank` the use `IF` !! – Rajesh Sinha Sep 25 '19 at 9:40
• @MátéJuhász I get a popup saying there is something wrong with my formula, e.g. syntax etc. – ProfK Sep 25 '19 at 10:30
• @RajeshS I am asking almost precisely how to return `0` instead of `#VALUE!`. Replacing a `0` with blank is trivial and after the fact. – ProfK Sep 25 '19 at 10:31
• @MátéJuhász Great catch, why not make it an answer? – ProfK Sep 25 '19 at 10:50
• ,, I've already said the reason above,, better you share sample data with us ,, help to fix the issue!! – Rajesh Sinha Sep 26 '19 at 5:44

Good Day,

Your formula looks like it should work, but...

Another option would be to try =IFERROR(E1*D1,0). It will calculate E1 x D1, and otherwise put a zero in.

Unfortunately, it might mask other errors, a number showing entered as text and it not doing the math. Interestingly trying to demonstrate that with an example the formula completed the math (treated it as a number regardless), but the sum formula a the bottom considered it text. Cell E4 was me playing with a double negative text value (date entry mistake) to see what would happen. • Thanks, `IFERROR` seems most useful to me here. – ProfK Sep 26 '19 at 7:25

Most of my Rate cells are empty, causing most of column F to just display, `#VALUE!`

Empty cells in calculations are considered to be `0` by Excel. You most likely don't have empty cell, but:

• formula resulting empty string (`""`), or
• just a single apostrophe (`'`)

I thought I'd replace my formula with `=IF(ISNUMBER(E1), E1 * D1, 0)`, but I get a popup saying there is something wrong with my formula, e.g. syntax etc

Depending on your regional settings you might need to use `;` instead of `,`.

• Good Day, Your formula looks like it should work, but... – BradR Sep 25 '19 at 16:31
• Thanks, I did have to use `;` not `,`, but @BradR's answer fits my problem better, with the `IFERROR` function. – ProfK Sep 26 '19 at 7:25