Excel 2003 Formula Error

Gadgetman496

I have a Total cell on an Excel spreadsheet which contains the following formula

=IF(SUM(G205)>0,(G205+G206+G207),"""")

It works fine with one exception, if the cell G207 is empty (no value) the Total cell returns a Value error.

There will be occasions where cell 207 will be empty so what should the above formula be to allow for that?

Healio

Could you put a 0 in g207?

MatureStudent

I don't have Excel 2003,so can't guarantee that this will work, but something similar should.

Replace (G205+G206+G207) in your formula with
IF(ISNUMBER(G207),SUM(G205:G207),SUM(G205:G206))
which will add only 205 and 206 if 207 does not contain a number.

Gadgetman496

Healio wrote: »

Could you put a 0 in g207?

Nice idea Healio but it won't work that way

coffee_cake

i'd wrap the whole thing in an if(iserr( to filter out g207
if(iserr(IF(SUM(G205)>0,(G205+G206+G207),"""")),IF(SUM(G205)>0,(G205+G206)),IF(SUM(G205)>0,(G205+G206+G207),""""))

Triangla

Try this:

=IF(G205>0,G205+G206+G207," ")

coffee_cake

actually why are you summing one cell?
you can replace that with if(g205>0)

Gadgetman496

MatureStudent wrote: »

I don't have Excel 2003,so can't guarantee that this will work, but something similar should.

Replace (G205+G206+G207) in your formula with
IF(ISNUMBER(G207),SUM(G205:G207),SUM(G205:G206))
which will add only 205 and 206 if 207 does not contain a number.

Top marks MatureStudent, your formula just needed one extra bracket at the end and it worked perfectly. Really appreciate the help

IF(ISNUMBER(G207),SUM(G205:G207),SUM(G205:G206)))


Can I trouble you with one last issue? I forgot about one more cell that needs to be included in the Total cell formula but it too may at times be empty, it's cell A15

Can you adjust the above formula to include cell A15 under those conditions?

coffee_cake

Didn't see MS' post - much less clumsy than mine

Gadgetman496

Triangla wrote: »

Try this:

=IF(G205>0,G205+G206+G207," ")

No, that doesn't work either unfortunately.

MatureStudent

Sorry about the ), I forgot about the one at the start.
Is there any relationship between a15 and G207 - could they both be blank at the same time?

Gadgetman496

MatureStudent wrote: »

Sorry about the ), I forgot about the one at the start.
Is there any relationship between a15 and G207 - could they both be blank at the same time?

Yes both could be blank at the same time. In fact both will either have a value or both will be blank, there will never be an occasion where one will have a value and the other not. Maybe if I explain their functions

Cell A15 contains a figure

Cell G207 contains a percentage figure of A15 (a VAT Rate calculation)

MatureStudent

Replace SUM(G205:G207) with (SUM(G205:G207) + a15) which will also add a15 any time it finds a number in G207.

Gadgetman496

MatureStudent wrote: »

Replace SUM(G205:G207) with (SUM(G205:G207) + a15) which will also add a15 any time it finds a number in G207.

Which makes the formula

=IF(SUM(G205)>0,IF(ISNUMBER(G207),(SUM(G205:G207) + A15),SUM(G205:G206)))

And it works perfectly now.

Your assistance is greatly appreciated, thank you

MatureStudent

No problem, glad it worked.