Indicate Pairs based on Given %

[Platak]

Hi there,

I am putting together a battery log that keeps track of battery capacity in percentages. The goal is to pair two batteries with the closest percentages together. As you will see in the attached sheet, I have indicated those pairs with asterisks (*). Is there a way for the sheet to indicate pairs automatically? I am not sure how it would display, but I was thinking that it could indicate pairs by cell colour perhaps? I am open to ideas and look forward to hearing your solution!

* AliGW has solved the above for which I'm grateful. I should have noted earlier that an odd number of batteries may be logged in which case one battery can not be paired. One solution brings forward another problem, so it seems! I would not mind if the one odd battery gets displayed a blank cell. I will be tinkering with it in the meantime.

[AliGW]

How about this in H2 copied down:

=REPT("*",LOOKUP(RANK(G2,$G$2:$G$9,0),{1,3,5,7,9},{1,2,3,4,5}))

Administrative Note

Members will often tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. Thanks.

[Platak]

Thanks AliGW,

This is definitely a solution that works! A few remarks:

- I have added an IF statement as to not display the '#VALUE!' in the H cells. That is about the extend of my Excel skills!

- Is there a function that will work with an odd amount of inputs? This seems picky, but there is a good chance that I will have an odd number of batteries after which the following 'error' will present itself: when inputting the following values, it will pair F3 and F4 while it should pair F2 and F3 as they remain the closest in percentages.

'2400' in F2.
'2410' in F3.
'2500' in F4.

I have attached this example.

[AliGW]

You’ve just shifted the goalposts significantly. You’ll have to wait for someone with a different approach. Sorry.

[Platak]

I really appreciate the changes you made to the log. It is very usable now and will be using it with the battery data I have so far. Very happy with it!

Also, I noticed that it only pairs incorrectly when the odd one out has a higher capacity.

2450 *
2450 *
2500 *

2450 *
2450 *
2400 **

------------

2450 **
2460 *
2500 *

2450 *
2460 *
2400 **