Hi there
I can do this in a clunky way (I think) but I'm looking for a more sensible
method. I have an output spreadsheet from an instrument, and need to tinker
with the numbers to display different things depending on the value of the
cell.

If the value is -ve, I display 'ND'; if the value is <0.2, I display TR.
Otherwise then umber is displayed. So far so good.

I then need to sum a number of these cells (say 4 of them). If there are any
numbers (ie not all of the cells are ND or TR), I sum the numbers, ignoring
ND or TR; if all the cells are ND I display ND, if all the cells are TR, or a
mixture of ND or TR, I display TR. This is where I get stuck! Any ideas?

Thanks in advance

Hi Gavin,

I assume that by displaying "TR" you mean assigning value "TR" using if
or any other condition.

You can incorporate countif into your formula. I mean
countif(a1:a4,"tr") will give you number 0 if any "tr" is found in
the range.

Regards,
Ivan

Hi
The individual cell IF statement is (e.g.)
=IF(V48<0,"ND",IF(V48<0.2,"TR",V48)), where V48 is the original value.
If we assume I'm then summing V46 to V49, say, I need to check if there are
any numbers, and sum them (sum=sum of numbers, ignoring ND & TR); or all ND
(sum=ND); or all TR or mix ND/TR (sum=TR).
Do you think I need nested countif's for ND& TR's?


" wrote:

Hi Gavin,

I assume that by displaying "TR" you mean assigning value "TR" using if
or any other condition.

You can incorporate countif into your formula. I mean
countif(a1:a4,"tr") will give you number 0 if any "tr" is found in
the range.

Regards,
Ivan

Hi,

you don't need to check for numbers, sum ignores non-numerics.

Supposing, that formula =IF(V48<0,"ND",IF(V48<0.2,"TR",V48)) is in W48
(better to tell in range W46:W49, and you need the total in W50 and
following:
W50 = sum(W46:W49) - sum of numbers
W51 = sumif(W46:W49,"ND",v46:v49) - sum of ND
W52 = sumif(W46:W49,"TR",v46:v49) - sum of TR

Does this help? Maybe I didn't understand your question fully (my
understanding changed after your second post).

Regards,
Ivan

Hi Ivan
We're almost there, but maybe I'm not explaining very well! Here's some
examples
0.5,0.5,0.5,0.5: Sum=2
0.5, 0.5, TR, ND: Sum = 1.0
TR,TR,TR,TR: Sum = TR
TR,ND,ND,TR: Sum = TR
ND,ND,ND,ND: Sum = ND
with obviously numerous variations.
Thanks for the quick response


" wrote:

Hi,

you don't need to check for numbers, sum ignores non-numerics.

Supposing, that formula =IF(V48<0,"ND",IF(V48<0.2,"TR",V48)) is in W48
(better to tell in range W46:W49, and you need the total in W50 and
following:
W50 = sum(W46:W49) - sum of numbers
W51 = sumif(W46:W49,"ND",v46:v49) - sum of ND
W52 = sumif(W46:W49,"TR",v46:v49) - sum of TR

Does this help? Maybe I didn't understand your question fully (my
understanding changed after your second post).

Regards,
Ivan

Gavin,

Try something like this.

=IF(SUM(F6:I6)0,SUM(F6:I6),IF(AND(COUNTIF(F6:I6," TR")0,COUNTIF(F6:I6,"ND")0),"TR",IF(AND(COUNTIF( F6:I6,"TR")0,COUNTIF(F6:I6,"ND")=0),"TR","ND")))

I used F6:I6 for my range to evaluate, change those as needed. In your
sample data this returned 2,1,TR,TR and ND respectively. This assumes
that if there are no numbers, there will always be "TR"'s and/or "ND"'s
in the cells.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=538382

Steve
That looks perfect, just what I was after
Many thanks
Gavin

"SteveG" wrote:


Gavin,

Try something like this.

=IF(SUM(F6:I6)0,SUM(F6:I6),IF(AND(COUNTIF(F6:I6," TR")0,COUNTIF(F6:I6,"ND")0),"TR",IF(AND(COUNTIF( F6:I6,"TR")0,COUNTIF(F6:I6,"ND")=0),"TR","ND")))

I used F6:I6 for my range to evaluate, change those as needed. In your
sample data this returned 2,1,TR,TR and ND respectively. This assumes
that if there are no numbers, there will always be "TR"'s and/or "ND"'s
in the cells.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=538382

Gavin,
Glad I could help.

Regards,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=538382