1

I have a spreadsheet which has a large number of rows. Each row has 3 columns (B,C,D) that can contain numerical data that has been collected at three different points in time. B = first time point, C = second time point, D = third time point. When it was not possible to collect data at a given time point, the cell contains a zero.

For each row, I would like to

  1. Create a 'Total Column (column E) for each row.
  2. Enter 'no result' in the total column (column E)for any row in which only contains one number (irrespective of whether this is in column B, C or D)
  3. Subtract the number in the latest data collection time point that contains a number from the number in earliest data collection time point column that contains a number

Can anyone help?

1
  • 2
    As you said there are 3 time points, are the 3 time points regular?
    – Lee
    Mar 18, 2021 at 8:40

3 Answers 3

4

You can use an array formula with a combination of IF, COUNTIF, LOOKUP and INDEX functions.

Cell E2 (total column) formula:

=IF(COUNTIF(B2:D2,">0") < 2, "no result",LOOKUP(9.999999999E+307,IF(B2:D2>0,B2:D2)) - INDEX(B2:D2,MATCH(TRUE,INDEX(B2:D2>0,0),)))

While in the formula, make it an array formula by pressing CTRL + SHIFT + ENTER to get those curly brackets. Drag it down.

First it's counting the number of values over 0 in the row. If there are less than 2, then display "no result". Else, run the remaining part of the formula. The remaining part is looking for the last non-zero value in the row and subtracting the first non-zero value in the row.

enter image description here

1
  • 3
    Upvoted for the generalised solution which would extend to any number of columns.
    – AdamV
    Mar 18, 2021 at 13:49
2

Welcome Stephen. While Isolated's answer is boss level and could be extrapolated for any number of input columns, this simplified formula should also work for simplest 3 column input scenario

=IF(COUNTIF(B2:D2,">0") < 2, "No result", IF(B2=0,D2-C2,IF(D2=0,C2-B2,D2-B2)) )
1

Given the small number of columns, I would be inclined to keep it really simple so anyone can follow the logic and troubleshoot it. Such as:

=IF(COUNTIF(B2:D2,">0")<2,"No result", IF(D2>0,D2,C2)-IF(B2>0,B2,C2))
4
  • 1
    Lol. I at first thought I submitted by own answer twice! Your code would win for occupying the least amount of bytes in memory, whereas mine would win for fast execution (only one IF, one third of the time) Mar 18, 2021 at 14:11
  • 1
    Mine would win for fastest execution because it started five minutes earlier! ;-)
    – AdamV
    Mar 23, 2021 at 13:51
  • 1
    How is this for beauty =MAX(B2:D2)-MIN(B2:D2+BITLSHIFT(NOT(B2:D2),31)). Returns the difference between max and min with one or less zero inputs, returns zero if two inputs are zero, and a large negative number if all inputs are zero. Mar 23, 2021 at 14:09
  • 1
    I don't think MIN and MAX should be confused with leftmost and rightmost. I don't see anything in the scenario which says the data will always increase or decrease over time - C might contain the highest or lowest number while B or D contain the earliest and latest.
    – AdamV
    Mar 24, 2021 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.