#DIV/0 help

[masond3]

Hey guys

Quick question, these are my formulas

I need to put an isserror into my formula to stop #div/0 any ideas where i can put this ?

=ROUND(SUM('CTO < £250k'!AV116)/1000,0)+ROUND(SUM('CTO > £250k'!AV116)/1000,0)&"k"& " (3yrs @ 15/mth)"

=(('CTO < £250k'!AV120)+('CTO > £250k'!AV120))/2

[Andrew-R]

As you're never dividing by 0 in those formula the only way you can get a DIV/0 error is if that's the contents of cells AV116 or AV120 on the CTO < £250k sheet.

That being the case you'd be better off fixing that sheet. However, you could simply modify your formula to:

=ROUND(SUM(IFERROR('CTO < £250k'!AV116,0))/1000,0)+ROUND(SUM(IFERROR('CTO > £250k'!AV116,0))/1000,0)&"k"& " (3yrs @ 15/mth)"

And

=IFERROR((('CTO < £250k'!AV120)+('CTO > £250k'!AV120))/2,0)

Is that what you're after?

[Fotis1991]

In front of your formulas.


=IFERROR((('CTO < £250k'!AV120)+('CTO > £250k'!AV120))/2,"")

=IFERROR(ROUND(SUM('CTO < £250k'!AV116)/1000,0)+ROUND(SUM('CTO > £250k'!AV116)/1000,0)&"k"& " (3yrs @ 15/mth)","")

[masond3]

HI Andrew/ Fotis1991

Thats exactly what i need, i need help on the these additional three,

if you dont mind helping

=ROUND('CTO < £250k'!AV5,0)+ROUND('CTO > £250k'!AV5,0)&" (3yrs)"
=ROUND('CTO < £250k'!AV26/1000000,0)+ROUND('CTO > £250k'!AV26/1000000,0)&"m"&" (3yrs)"
=ROUND(('CTO < £250k'!AV28/1000000),1)+ROUND(('CTO > £250k'!AV28/1000000),1)&"m"&" (3ys)"

[Andrew-R]

It should work the same with all of these.

You start off with:

=YourFormula

And end up with:

=IFERROR(YourFormula,"Error message/blank/whatever")

[masond3]

Hi Andrew R

i am really struggling changing this formula

=ROUND(('CTO < £250k'!AV28/1000000),1)+ROUND(('CTO > £250k'!AV28/1000000),1)&"m"&" (3ys)"

I have tried =round(sum(IFERROR('CTO < £250k'!AV28/1000000)+round(sum(IFERROR('CTO > £250k'!AV28/1000000)&"k"& " (3yrs @ 15/mth)"

but getting getting errror messages

would appreciate any help available

[Andrew-R]

You've got your brackets all mixed up.

IFERROR checks to see if an expression returns an error; if it does not then it returns the results of the expression, if it does it returns a specified value. So the syntax is:

=IFERROR(<expression>, <error value>)

So what you want is:

=IFERROR(ROUND(('CTO < £250k'!AV28/1000000),1)+ROUND(('CTO > £250k'!AV28/1000000),1)&"m"&" (3ys)","")

Where the section is bold is your entire original formula and the double quotes at the end indicate that a blank value is returned if the formula returns an error.

[martindwilson]

fyi
=ROUND(SUM('CTO < £250k'!AV116)/1000,0)+ROUND(SUM('CTO > £250k'!AV116)/1000,0)

is the same as
=ROUND('CTO < £250k'!AV116/1000,0)+ROUND('CTO > £250k'!AV116/1000,0) no need for sum in there