WeThotUWasAToad
asked on
Modified Max() formula depending on values in adjacent column in Excel
(Edit note: Apologies for the images being so large but I don't know of a way to reduce their size.)
Hello,
In Excel, how do you modify the =Max() function (or some other function) so that it evaluates only those cells in a given range for which an adjacent cell meets some specified criteria?
For example, suppose you have a spreadsheet in which Column C contains simple numbering from 1 to 60 (as a timeline in seconds) as shown in Fig. 1. And suppose Column D contains production values beginning in the 1st row and continuing down to some other row but then only zeros in the cells below that.
Production continues for 6 seconds in Fig. 1 and 9 seconds in Fig. 2:
If the total production is displayed in Cell D2 (Fig. 3), what formula in Cell C4 will display the total number of seconds during which production continued so that the production rate calculated in Cell D6 is accurate (Fig. 4)?
In other words, what formula in C4 will display the last second marker in Column C which has an associated Column D value not equal to zero?
Thanks
Hello,
In Excel, how do you modify the =Max() function (or some other function) so that it evaluates only those cells in a given range for which an adjacent cell meets some specified criteria?
For example, suppose you have a spreadsheet in which Column C contains simple numbering from 1 to 60 (as a timeline in seconds) as shown in Fig. 1. And suppose Column D contains production values beginning in the 1st row and continuing down to some other row but then only zeros in the cells below that.
Production continues for 6 seconds in Fig. 1 and 9 seconds in Fig. 2:
If the total production is displayed in Cell D2 (Fig. 3), what formula in Cell C4 will display the total number of seconds during which production continued so that the production rate calculated in Cell D6 is accurate (Fig. 4)?
In other words, what formula in C4 will display the last second marker in Column C which has an associated Column D value not equal to zero?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
You made it simple. It seems I overthought it. Lol
I assumed there maybe zeros in between the data as well.