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?

Stephen Dodd
  • 11
  • 1

3 Answers3

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

Isolated
  • 1,505
  • 1
  • 3
  • 6
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)) )
ExcelEverything
  • 3,114
  • 1
  • 4
  • 16
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))
AdamV
  • 6,177
  • 2
  • 22
  • 38
  • 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) – ExcelEverything Mar 18 '21 at 14:11
  • 1
    Mine would win for fastest execution because it started five minutes earlier! ;-) – AdamV Mar 23 '21 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. – ExcelEverything Mar 23 '21 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 '21 at 08:04