Sum around a referenced cell

[zamunda1]

I have a spreadsheet with multiple columns of information, however three are of specific interest: price, quantity sold, and total revenue (p x q). There can be 500+ rows of data to this spreadsheet.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’ve then set up a v-lookup function to find the maximum total revenue. It’s set to identify the price, quantity and total revenue in a given row. Let’s call this row of information a “hot row.” Certainly as sales progress, this row of information could / should change.
<o> </o>
What I'm trying to do is analyze the information above and below this “hot row / reference cells.” Price is listed in descending order within the columns of data.
<o> </o>
Simple example: Let’s say there were 30 units sold for $5.25, 20 units for $5.10, 100 units for $5.00, 10 units for $4.00, 20 units for $3.50. Clearly, the 100 unit row of information is the winner. Total units are 180 with 50 units above and 30 units below.
<o> </o>
How would I set this automation up as the "hot row" will change with new, data input? It’s clearly a simple sum technique, but I can't figure out how to use that reference point to manipulate around the data.

 

[Gerald Higgins]

OK thanks.

We need to be careful about this.

In the data you've provided in post #10, the values in column A are all pretty similar.
Total 34 above the line, 32 below the line.
So, one way to arrive at the result is to look for the values that cover 35% above the line, and a separate calculation for the 35% below the line (in both cases INcluding the line itself), giving you 70% in total.

BUT, I think it's POSSIBLE that the data could be more imbalanced.
For example, what if 50% was above the line, and 20% was below it. What do you want to do then ?

Try experimenting yourself with very different values in Col A, and decide what you want to do with them, then post back.

 

[zamunda1]

You are correct. A distortion can occur, so a simple % that you suggest may not work. Is there an actual way to count?

 

[Gerald Higgins]

There's various ways to do this.

I think the first thing to do is for you to experiment with your data - have a look at different scenarios where the data is not evenly balanced, and decide for yourself how you want to deal with that imbalance.

Once you've done that, we may well be able to build something in Excel that does it for you. But I think we need your decision first, in terms of how you want to deal with the imbalance.

 

[zamunda1]

The distortion is not the issue - - I just need to identify the upper and lower limits of where it occurs. Whatever solution you can offer - I'm willing to try. Remember, the spreadsheet can have up to 1000 rows of data

 

[Gerald Higgins]

zamunda1 - sorry, but I think we're now going about this the wrong way round.
You're asking me to come up with something, without telling me exactly what you want the results to be.

I think you need to consider for yourself, what exactly do you want to do if the data is very imbalanced ?

Can you please make up a small set of typical data which is very imbalanced, post that data here, and post details of what, exactly, you want the solution to do.

 

[zamunda1]

Gerald - I've figured out how to accomplish my task. I had to add another column and create a running total. From there, I was able to come up with the high and low points via addition and subtraction from the hot point. The imbalance isn't the concern - it's just how to accomplish and determine the reference points I'd requested. Thank you for the help on this. This site is awesome!

 

[Gerald Higgins]

OK great, thanks for coming back to let us know