1

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?

5
  • 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
  • 1
    @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
1

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.

I hope this helps, Brad

1
  • Thanks, IFERROR seems most useful to me here. – ProfK Sep 26 '19 at 7:25
2

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 ,.

2
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.