Excel Spreadsheet Formula

September 2, 2009 at 10:29:58
Specs: Windows XP
I made an Excel Spreadsheet that is a list of products, the number of each product, and the value of each. What I want to do is use conditional formatting to highlight the products with the highest value, up to a certain total worth. There are 3 columns - Product name (A), Amount of (B), and Value (C). There are 41 rows total (40 products). Basically I need Excel to find the items in C that are the highest, multiply those values by the B in the same row to find the TOTAL value of that particular product, and add up those values to a certain point ($5,000) highlighting all of the item names (in A) that are included in the sum. Is this possible? I'm convinced it is, but I cannot find the right formula. If my description is confusing, I can try to re-word it. Thanks in advance!

See More: Excel Spreadsheet Formula

Report •

September 2, 2009 at 13:04:25
Doing this with Conditional Formatting and formulae is going to be extremely cumbersome. The conditional formatting of a cell is determined by an specific event (a condition) that can be evaluated to either TRUE or FALSE. Since you don't know which cells will be involved in the SUM, you have to determine that first and then have CF "find" those cells.

In other words, how would you tell CF to format A1 & A3 only if A1 and A3 are included in the group of cells in the SUM function?

Let's look at the formula that would be needed to actually find which values would be included in the SUM. I'm not going to worry about multiplying C * B, I'm just going to use straight numbers so you can see the issue. In addition, I'm only going to use 3 numbers...I don't know how many you'll need to reach that $5000 mark.

Here's my data:

1   2000
2   1000
3   2500

To find the highest values that sum to <=5000, I could use this to formula to display the actual values in a cell.

=IF(SUM(LARGE(A1:A3,1),LARGE(A1:A3,2))>5000,LARGE(A1:A3,1),IF(SUM(LARGE(A1:A3,1),LARGE(A1:A3,2),LARGE(A1:A3,3))>5000,LARGE(A1:A3,1)&" "&LARGE(A1:A3,2),A1&" "&A2&" "&A3))

which in this case would return:

2500 2000

Basically what it does is add up the 2 highest values and if the sum is greater than 5000, it will display just the highest value. If the sum of the 3 highest values is greater than 5000, it will display the 2 highest, and finally if the sum of all 3 values is less than 5000, it will display them all.

Of course the issue gets even more complicated if you have a large number of values that will be included in the sum, since you can only have 7 Nested If's in a single if statement.

OK, now that I've got the values, I need to do something within CF to find those values in the actual list and CF them.

There are multiple ways to attack this, but none of them are straighforward.

This seems more like something that should be handled via VBA.

Report •
Related Solutions

Ask Question