creating formulas which ignore text in the relevant cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

help with excel formulas - 31 Dec 2007 15:31 GMT

How can you create a formula which will ignore text in the cells the formula
is relevant to?

i.e i have a row of numbers which SUM will add up as a formula, however
sometimes i need to have a number plus a letter in the same cell as soon as i
do this the formula ignores the number and creates an error

Any help?

Reply to this Message

CLR - 31 Dec 2007 15:47 GMT

Depending on exactly how your TEXT string is constructed, something like this
should work..........

=(LEFT(A1,LEN(A1)-1)*1)+3

Vaya con Dios,
Chuck, CABGx3

> How can you create a formula which will ignore text in the cells the formula
> is relevant to?
[quoted text clipped - 4 lines]
>
> Any help?

Reply to this Message

tedmi - 31 Dec 2007 15:51 GMT

The easiest way to do this is to spread your data over three columns, like so:
Col.A contains just numbers
Col.B contains just the text that may get combined with numbers
Col.C contains the formulas A1&B1, copied down
Run your sum on Col.A
For reporting purposes, you can hide Cols A and B, and put the Sum(A1.Axx)
function at the foot of Col.C

Signature

TedMi

> How can you create a formula which will ignore text in the cells the formula
> is relevant to?
[quoted text clipped - 4 lines]
>
> Any help?

Reply to this Message

CLR - 31 Dec 2007 15:57 GMT

Good answer, TedMi.........I misread the OP's post

Vaya con Dios,
Chuck, CABGx3

> The easiest way to do this is to spread your data over three columns, like so:
> Col.A contains just numbers
[quoted text clipped - 12 lines]
> >
> > Any help?

Reply to this Message