Calculations on non-contiguous
cells
We are used to do calculations in Excel
on an array of cells, where the cells are contiguous.
But what if we want to do some operations on non-adjacent cells?
Take, for example, this scenario:
We want to find the average of all
positive numbers in these cells, in the picture:
Pic
1: Non-contiguous cells
Solution:
We solved the problem in two easy
steps:
Step 1:
We define the whole range surrounding the cells as a named range:
The named range is NUM and it refers to
the range A1:L4
Pic 2: The Named Range
Step 2:
The formula in D7 will yield the desired
result.
=SUMIF(NUM,">0")/SUM(IF(NUM>0,1,0))
This solution is dynamic in the sense
that you can, of course, add numbers to the defined range, erase numbers or modify
them.
But you don't need to touch the formula!
Pic 3: one formula that doesn't change when the data change
אין תגובות:
הוסף רשומת תגובה