Text in a calculation

[ExcelRoy]

Hi,

is there a way to ignore text in a formula

=IF(F22="","",SUM(G22-H22))

G22 on this formula is set to "Not Set" as there is no number to calculate

Could the calculation be "tricked" into thinking it actually is a 0 and therefore calculate as required?

Many Thanks

 

[Drrellik]

=IF(F22="","",SUM(G22:H22)) change the- to : and see if you still get an error

 

[Jonmo1]

So if G22 is "Not Set"
You would want G22 to be treated as Zero, therefor 0-H22
So if H22 was say 10, you'd get -10

Is that what you want?

Could there be text in either cell, G22 or H22 ?
Should the same effect occur for both cells?

 

[Drrellik]

sorry I thought it was a range are you trying to subtract one cell from the other.

 

[ExcelRoy]

No worries Drrellik,

Yes Jonmo spot on

Only text may be in G22 never in H22

Thanks

 

[Jonmo1]

Try

=IF(F22="","",SUM(G22)-H22)

 

[ExcelRoy]

That's perfect,

Thanks Jonmo

 

[jdubbs41]

I am trying to build a weekly time sheet with in and out times, as well as "vacation" and "off" text in the cells, but when I sum everything up, I get a value error. Is there a way to use a function in addition to the sum function to tell excel the text is either zero or a number?

 

[Drrellik]

JDubbs,

you might want to start a new thread rather than post here, on an older thread that has nothing to do with what you are asking about. it wont get seen by the whole forum. and I am sorry I can't move it for you.