Any Excel Heads Here?

Sid 1984

Scratching my meagre Excel head with this one….

Cell E11 is total of E2:E10.

Cell D11 is E11 converted from Kms to Miles. =IF(E11="","",CONVERT(E2,"km","mi")) But this currently also shows 0 as E11 is filled.

I want the D11 & E11 to be blank if there is nothing in E2:E11.

I tried =IF(E2="","",SUM(E2:E10)) which works fine if you enter a value in E2, but obviously if you enter anything in any other cell in the column, except E2, E11 stays blank.

I also tried using IF & ISBLANK with the range E2:E10 but it didn't like that either.

Any ideas?

Many thanks

LambshankRedemption

Could you put up a demo of what you have? As in not the real data but same layout?

Sid 1984

https://www.boards.ie/discussion/comment/124063987#Comment_124063987

Balls!

Forgot to attach the screenshot….it's early, it's Friday…😂

LambshankRedemption

The easy way I use for converting KMs to miles is multiplying by .6. So 5km is 3 miles for example. So thats all Ive done..

Edit, so E11 is =SUM(E2:E10)

D11 is =E11*0.6 to convert it to miles. With nothing in E2:E10, then they both just show zero.

What more do you want?

Lu Tze

I don't have Excel open to double check, but you could change this =IF(E2="","",SUM(E2:E10)) to =IF(SUM(E2:E10)=0,"",SUM(E2:E10))

CoffeeImpala

The convert formula in D11 should presumably also reference E11 rather than E2.

Sid 1984

https://www.boards.ie/discussion/comment/124064124#Comment_124064124

It does, typo, feckin' hate Fridays…😁

Sid 1984

https://www.boards.ie/discussion/comment/124064054#Comment_124064054

Perfect!

That will come in very useful, thank you so much!

Esel

https://www.boards.ie/discussion/comment/124064037#Comment_124064037

0.6 is incorrect. You should use 0.6214 for an accurate conversion.

LambshankRedemption

FFS!

Esel

https://www.boards.ie/discussion/comment/124066343#Comment_124066343

Why use an incorrect conversion rate? You end up with the wrong answer.

LambshankRedemption

https://www.boards.ie/discussion/comment/124066346#Comment_124066346

I said the simple conversion I do is multiply by .6. It's simple because you can do the sums in your head and then can see if excel is giving approximately the right answer. My number is the same as yours just to one decimal place not four decimal places.

If your calculations require 4 decimal places, then great.

db

the contents of E11 will never be blank because it contains a formula. The SUM formula in E11 will return 0 if the range of the SUM is empty.