How do I get this to ignore text?

[crof_s]

How would I get this formula to ignore the text in cell C4? I keep getting a #VALUE ! error. I want the formula to just use the numbers in the cell.

=IF(SUM(C4*5+D4)*120%>50,SUM(C4*5)*120%,SUM(C4*5+D4)*120%)

Thanks in advance.

 

[Dryver14]

what exactly is in C4?

 

[Aladin Akyurek]

Try...

=IF(SUM(C4*5,D4)*120%>50,SUM(C4*5)*120%,SUM(C4*5,D4)*120%)

 

[crof_s]

Try...

=IF(SUM(C4*5,D4)*120%>50,SUM(C4*5)*120%,SUM(C4*5,D4)*120%)

Nope. Still the same.

 

[crof_s]

what exactly is in C4?

n C4 there is:

=4.99&" +vat"
I

 

[crof_s]

n C4 there is:

=4.99&" +vat"
I

Sorry I dunno where that 'I' at the bottom came from. that's not in there. In C4 there is only:

=4.99&" +vat"

 

[Aladin Akyurek]

n C4 there is:

=4.99&" +vat"
I

Nope. Still the same.

=IF(SUM(N(C4)*5,D4)*120%>50,(C4*5)*120%,SUM(N(C4)*5,D4)*120%)

Why is there text in C4 which is used in a numerical formula?

 

[crof_s]

=IF(SUM(N(C4)*5,D4)*120%>50,(C4*5)*120%,SUM(N(C4)*5,D4)*120%)

Why is there text in C4 which is used in a numerical formula?

The majority of numbers in the column include tax (vat). Cell C4's number does not. I would like to show this on the sheet. Otherwise the total at the end of row 4 will not correspond with the formula. It would show a total WITHOUT vat while the rest of the rows would show a final price WITH vat in the 'totals' column.

Anyway, does it really matter why? I just want it to ignore the text.

 

[Aladin Akyurek]

The majority of numbers in the column include tax (vat). Cell C4's number does not. I would like to show this on the sheet. Otherwise the total at the end of row 4 will not correspond with the formula. It would show a total WITHOUT vat while the rest of the rows would show a final price WITH vat in the 'totals' column.

Anyway, does it really matter why? I just want it to ignore the text.

It does matter... But why don't you evaluate the suggestion?

=IF(SUM(N(C4)*5,D4)*120%>50,(N(C4)*5)*120%,SUM(N(C4)*5,D4)*120%)

 

[crof_s]

It does matter... But why don't you evaluate the suggestion?

=IF(SUM(N(C4)*5,D4)*120%>50,(N(C4)*5)*120%,SUM(N(C4)*5,D4)*120%)

Nope. For some reason when there is only 4.99 in the C4 cell then my total is fine which is £41.88 when using the formula you just provided. However, once I add the letters in the C4 cell I come up with £11.94?