CODEWITHSUNDEEP
excel
WesZ
WesZ

Reputation: 139

How to formulate Dynamic Excel Ranges

Column C   Column D   Column E
  14          6            1
  11                   16.52
   5                   82.64
  30                       0
  60                       0
  12                       0
   9                       0
   4                       0

I have these three columns and they are all user input so they are numerically dynamic. Column C has a range of numbers, some less than the value in Column D. Column E has values greater than 0. The 0 in Column E signifies do not calculate the same row in Column C. What I need to do is take the corresponding Non-Zero values in Column E and test the values in Column C to be less than Column D. Then take the difference between Column D and any values in Column C less than Column D and divide that value by the COUNT of Column C equal or greater than Column D. For example as it is now. In Column C I would not evaluate Row 5:9 because Column E has 0 in those rows. Column C Row 4 is the value 5, which is 1 less than Column D 6 so the difference is 1. There are 2 values in Column C that are equal or greater than Column D so the result is 1/2 = .5

Another Example:

Column C   Column D   Column E
  14          6            1
  11                   16.52
   5                   82.64
  30                       0
  60                       0
  12                       0
   9                       0
   4                   19.56

In this example there are 4 used values in Column C Row 2,3,4,9. Two of the values are less than Column D by D2-C4 + D2-C9 = 3. The result is 3/2 = 1.5

Another Example:

Column C   Column D   Column E
  14          6            1
  11                   16.52
   5                       0
  30                       0
  60                       0
   6                   18.66
   9                       0
   4                       0

The result is 0/3 = 0

I've tried to figure something out with INDEX/MATCH, SUMPRODUCT, COUNTIFS, SMALL, INDIRECT, MIN, but this is beyond my EXCEL capabilities.

Upvotes: 0

Views: 43

Answers (1)

user11246173
user11246173

Reputation:

A SUMIFS, a couple of COUNTIFS and some math should do it.

=(D2*COUNTIFS(E:E, "<>"&0, C:C, "<"&D2)-SUMIFS(C:C, E:E, "<>"&0, C:C, "<"&D2))/COUNTIFS(E:E, "<>"&0, C:C, ">="&D2)

Upvotes: 2

Related Questions