In the attached spreadsheet I have data that is in cells A1:A6 totaled in cell A9.
In cell C9 I have a manual typed number 7 which is the sum of A1 & A5.
Question is how do I get A1 and A5 to highlight. Above example is pretty simple to find manually but it gets harder when there is more data.
I can't find a fuction anywhere that could help me.
Also I get a free lunch if I can figure this out.
Last edited by ice; 03-08-2010 at 06:39 PM.
Ok, you can't do this (to my knowledge) in conditional formatting without a helper column. The helper column can have =IF(AND($C$9=2*A1,COUNTIF($A$1:$A$6,A1)=1),"No",IF(OR(A1+($A$1:$A$6)=$C$9),"Yes","No")), confirmed with Ctrl+Shift+Enter. It will only work for values that add up to the test value with only one other number. If there are multiple ways to do it, all of them will return "Yes". The conditional formatting formula could just highlight based on whether the cell says "Yes".
you are going to need solver and conditional formatting. see attached
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Ice, there are distinct advantages and disadvantages with Martin's and my approaches. The advantage of his is that it will work for any number of cells that it needs to match up to a given result. If that means three or four different cells must be used, his will work. Mine will only work with pairs of numbers.
The advantage of mine is that it will highlight multiple ways to do it, although it will not tell you which of the multiple ways each number goes with, whereas Martin's will only return one way. Just an FYI to help you decide which to go with.
in addition solver has its quirks even if you set contraints to binary i.e expecting 1 or 0 sometimes it returns things like 0.0000000009 or 1.0000000004 and then the formating wont work. it can be tweaked to stop that, however only 200 records(rows) can be checked.
however if you need more
http://www.solver.com/suppstdsizelim.htm offers a solution (at a large $ cost!)
Last edited by martindwilson; 03-08-2010 at 08:02 PM.
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks