Excel formula needed which includes multiplying or returning text

[kwillo]

Hi all,

I wonder if someone could help me with a formula. I have 2 columns which will either have a number in it or text "Not applicable" such as:

2 2
3 3
2 Not applicable

I need a formula which will multiply the 2 numbers together or return Not applicable if that is what is recorded in the cell. I came up with this =IF(D3>0,D3*E3,"Not applicable") which multiplies the numbers ok but just returns #VALUE!

Any guidance would be much appreciated.

Thank you!

 

[WaterGypsy]

Re: Help: Excel formula needed which includes multiplying or returning text

Try:

=IF(ISNUMBER(d3),D3*E3,"Not Applicable")

 

[kwillo]

Re: Help: Excel formula needed which includes multiplying or returning text

Try:

=IF(ISNUMBER(d3),D3*E3,"Not Applicable")

Hi WaterGypsy,

Thank you for replying, its returned "Not applicable" in all of the cells though?

 

[WaterGypsy]

Re: Help: Excel formula needed which includes multiplying or returning text

hhmmmm .... maybe excel thinks your cells are text fields.... try this slight change

=IF(ISNUMBER(VALUE(d3)),D3*E3,"Not Applicable")

 

[kwillo]

Re: Help: Excel formula needed which includes multiplying or returning text

hhmmmm .... maybe excel thinks your cells are text fields.... try this slight change

=IF(ISNUMBER(VALUE(d3)),D3*E3,"Not Applicable")

Hi WaterGypsy

That works perfectly thank you!!

Just one more quick query though what would I need to include if I have a number in one column i.e 3 and a not applicable in the second column but would want it to return the 3 still?

Thank you!

 

[WaterGypsy]

Re: Help: Excel formula needed which includes multiplying or returning text

You could try this ....

=IF(ISNUMBER(VALUE(D3)),D3,1)*IF(ISNUMBER(VALUE(E3)),E3,1)

It looks at the value in the first column and if it is a number uses that else uses 1...
then repeats for the second column

and multiplies the two results ... giving you what I think you want.

 

[kwillo]

Re: Help: Excel formula needed which includes multiplying or returning text

Hi WaterGypsy,

Thank you so much for your help so far!

I've used the above formula and it works perfectly but I just need to cover those columns that will have not applicable in both. I've tried adding on istext to your formula above so I've got:

=IF(ISNUMBER(VALUE(D3)),D3,1)*IF(ISNUMBER(VALUE(E3)),E3,1)*IF(ISTEXT(D3)),D3,"Not applicable)*IF(ISTEXT(E3)),E3,"Not applicable")

It's not liking something though, would you mind having a look at it for me?

Thank you!

 

[WaterGypsy]

Re: Help: Excel formula needed which includes multiplying or returning text

I'd simply put

=If(and(d3="Not Applicable", E3="Not Applicable"),"Not Applicable",IF(ISNUMBER(VALUE(D3)),D3,1)*IF(ISNUMBER(VALUE(E3)),E3,1))

Test for both being "Not Applicable" first and then do the multiplication if they aren't.