Convert inches to mm in column with 'Tr' also getting displayed.

[Keith (Southend)]

I'm currently adding local weather data which is hand written into an excel spreadsheet. This data is from 1915 to 1930.

The rainfall is recorded 'inches' at 9am and 3pm which added together = 24 hours.

I can add them together and convert to mm simply by multiplying the sum of the two (9am+3pm) by 25.4.

I have one anomaly I can't resolve some days just a 'Trace' is recorded 'Tr' which I want shown as 'Tr'.
If 'Tr' is in 9am or 15pm I want to return 'Tr'
If 'Tr' is in both 9am and 15pm I again want to return 'Tr'
If a value is in one or the other that 'Tr' is reported, that value stands.

I have attached a stripped down version of my spreadsheet where I have hand shown in column 'J' the end result I'm trying to acheive.
I've probably made this more complicated than it needs to be as well :-)

Many thanks

Keith (Southend)

[Glenn Kennedy]

Try:

=LET(v,IFERROR(1/(1/(E5:E35*25.4)),""),IF(v<>"",v,BYROW(C5:E35,LAMBDA(x,TEXTJOIN(,,,T(+x))))))

format as number and set dps as required.

[Keith (Southend)]

Thank you Glen, you both solved and simplified my anomaly.

Kind regards

Keith (Southend)
Ps: I'll tick the other boxes, so to speak :-)

[Glenn Kennedy]

Woo Hoo! Thanks for the feedback.