Styjun

What does i386 mean on macOS Mojave?

We are two immediate neighbors who forged our own powers to form concatenated relationship. Who are we?

Limit of an integral vs Limit of the integrand

Renting a house to a graduate student in my department

Why was the Ancient One so hesitant to teach Dr. Strange the art of sorcery?

Is there a need for better software for writers?

Pre-1993 comic in which Wolverine's claws were turned to rubber?

Will change of address affect direct deposit?

Washer drain pipe overflow

Is there a faster way to calculate Abs[z]^2 numerically?

Does Lawful Interception of 4G / the proposed 5G provide a back door for hackers as well?

Are there variations of the regular runtimes of the Big-O-Notation?

find not returning expected files

How do I tell my supervisor that he is choosing poor replacements for me while I am on maternity leave?

Should these notes be played as a chord or one after another?

Control variables and other independent variables

Why can't RGB or bicolour LEDs produce a decent yellow?

On what legal basis did the UK remove the 'European Union' from its passport?

On studying Computer Science vs. Software Engineering to become a proficient coder

Was there a contingency plan in place if Little Boy failed to detonate?

Thesis' "Future Work" section – is it acceptable to omit personal involvement in a mentioned project?

Ex-manager wants to stay in touch, I don't want to

Noob at soldering, can anyone explain why my circuit won't work?

Is there enough time to Planar Bind a creature conjured by a 1-hour-duration spell?

How to formulate Dynamic Excel Ranges

How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How do I properly clean up Excel interop objects?Import and Export Excel - What is the best library?Excel: Find the minimal value in a columnNegative values and postive values in excelHow to find two difference number values between A1 and B1 less than or equal to 10 value in Excelhow to populate an excel cell using multiple =IF functionExcel formula for greater than but less than with several tiersExcel doesn't calculate simple arithmetic operations correctlyCustom Filter in Excel Interop using c#

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

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.

excel

share|improve this question

asked Mar 23 at 10:46

WesZWesZ

547

add a comment | 

0

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.

excel

share|improve this question

asked Mar 23 at 10:46

WesZWesZ

547

add a comment | 

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.

excel

share|improve this question

asked Mar 23 at 10:46

WesZWesZ

547

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

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

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

share|improve this question

asked Mar 23 at 10:46

WesZWesZ

547

share|improve this question

asked Mar 23 at 10:46

WesZWesZ

547

asked Mar 23 at 10:46

WesZWesZ

547

asked Mar 23 at 10:46

WesZWesZ

547

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

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

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

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

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