Excel help please

Kerry_o'keefe

Can anyone help with this problem - please see below

<5.6 <5.6
23.1 24.8
100.5 98.6
75.2 68.9
>153 >153

In the above table, the figures represent results from an analyser. Each sample has been tested twice. In a third column, I would like to take an average of the 2 results BUT where the result is <5.6 record the answer as <20 and where the result is >153 report the result as >150. I have tried my limited Excel skills but clearly having a < or > causes any calculations problems. Tried IF and AVERAGE statements but not very good at this. <5.6 and >153 are the limits of this test. Can anyone help and I would fully understand if you cant.

Thanks

Any way Kerry, Excel numbskull

isofa

Obviously as you say the < and > are causing the problems.

You need really to either record just values, or strip out the additional character in a forumulae

Can you not just record 5.5 when the test result was <5.6 and similarly 154 when it was >153? Then you can set-up simple IF statements in the 3rd column to test and return your results as required.

Lakeuk

Examples:-

To make 153 be 150 use the following:-
=ROUNDDOWN(153,-1)

=ROUNDDOWN(CELL,-1)

To make >153 be 150 use the following:-
=ROUNDDOWN(REPLACE(">153",1,1,""),-1)

=ROUNDDOWN(REPLACE(CELL,1,1,""),-1)

Chippy_Minton

If the first result is in A2 and the second is in B2, enter the following formula in C2 and drag down:

=IF(A2="<5.6","<20",(IF(A2=">153",">150",AVERAGE(A2:B2))))

But what should it calculate if one sample result is <5.6 and the other is say 5.7? Or >153 and 152.8?

Kerry_o'keefe

Thanks to all for replying. Chippy - Absolutely brilliant!!!. The nature of the assay is such that a value(generally) that is less than 5.6 is usually <1(by virtue of the disease state we are talking about and same applies to top end of assay. Many thanks again