How to add top values in a grid only until total = 80

[Chats]

If anybody can suggest a way to tackle this problem I will be very happy:

I have a table in Excel, to keep things simple for now, 3 rows by 3 columns (the actual table is bigger) - something like this:

[tt]
| C1 C2 C3
==============
R1 | 12 05 22
R2 | 06 16 03
R3 | 01 09 35

[/tt]

What I need to do is shade the cells with the highest values, until the combined total of the shaded values >80

So in the example above, the following cells would be shaded:-

R3C3 = 35
R1C3 = 22
R2C2 = 16
R1C1 = 12

TOTAL = 85

So the final output would look like this:

[tt]
| C1 C2 C3
==============
R1 | [highlight]12[/highlight] 05 [highlight]22[/highlight]
R2 | 06 [highlight]16[/highlight] 03
R3 | 01 09 [highlight]35[/highlight]

[/tt]


I do not know whether I would be best tackling this with VBA or if there is a method that avoids VBA code. Either way I really am stuck at the moment as to how to start.

Has anybody tackled anything like this before - if so, how?

Thanks

Ade

 

[SilentAiche]

Chats,

How would you like to handle duplicate numbers? To alter your example slightly:

R3C3 = 35
R1C3 = 22
R2C2 = 12
R1C1 = 12

Total = 81

Which 12 would be highlighted in the grid?

Also, is the actual grid of a fixed size, or do you need a solution that accounts for the possibility that the size might vary? Are there any null cells?

Tim


[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[Chats]

Tim,

Good questions:-

Duplicate numbers would both be highlighted - so both cells with number 12 (R2C2 and R1C1 in your list) would be highlighted. I appreciate that this could potentially result in every cell in the table being highlighted if each one had, for example an identical value of 1.

The table is a fixed size at present - 5x5 cells. It may get larger in the future, but the solution does not need to respond to a "dynamic" re-sizing of the table. If there table needed to be larger I could re-write / modify the solution manually.

There are no null cells, the input is via scroll bar controls which limit input to a range between 0 and 100.

 

[Chats]

I think I have solved it!

I post below my solution, hopefully it will be helpful to somebody else one day... I hope my description makes sense and allows the solution to be recreated:

I have my 3x3 grid in range B35.

In cells F3 down to F11 I have the following formulas:-
=LARGE($B$3:$D$5,1) in cell F3
=LARGE($B$3:$D$5,2)
=LARGE($B$3:$D$5,3)
etc down to
=LARGE($B$3:$D$5,9) in cell F11

Now in cells I3 down to I11 I have the following formulas:-
=SUM($F$3:$F3) in cell I3
=SUM($F$3:$F4)
etc down to
=SUM($F$3:$F9) in cell I11

In cells J3 down to J11 I have the following formulas:-
=I3>80 in cell J3
=I4>80
etc down to
=I9>80 in cell J11

In cell J12 I have the formula
=COUNTIF(J3:J11,FALSE)

Finally, I have conditional formats based on the following forumla:-

=B3>=(LARGE($B$3:$D$5,($J$12)+1))

which I apply to each of the cells in range B35.

This all seems to work fine.

To expand the solution to a larger table than 3x3 would require some work to change forumlas and add rows, but shouldn't be too much of a problem, though if the table went much above 10x10 it would get a bit ungainly.

I'd still be interested if anybody has a smarter alternative!

Thanks
Ade