Help with duplicate values

[JHestter]

Hello all,

I've been stuck trying multiple ways to get a formula in col F to J to work.
I have a row of numbers in col A to E say; 5,5,4,3,2 and I need a formula that will add 1 to the second and third highest numbers and display that in col F to J as 5,6,5,3,2
using LARGE I keep getting 6,6,5,3,2 as its counting both 5s.

Thanks for any help

 

[jim may]

Give us 3 or 4 more examples so we can better understand. I dont think we are getting what you are saying/asking here...

 

[philwojo]

I think you expected outcome does not match your description, and as Jim asked maybe more data would help.

But if your expected outcome should be 5,6,5,4,3 you can try these formulas to get that.

In F2   ---   =LARGE($A2:$E2,1)
In G2   ---   =LARGE($A2:$E2,2)+1
In H2   ---   =LARGE($A2:$E2,3)+1
In I2   ---   =LARGE($A2:$E2,4)+1
In J2   ---   =LARGE($A2:$E2,5)+1

 

[JHestter]

I've been using this formula which works unless there's duplicates
=IF(OR(A1=LARGE($A1:$E1,2),A1=LARGE($A1:$E1,3)),A1+1,A1)
A:E contains 1,5,4,3,5
F contains the above formula dragged to J
if there's duplicates I want to count the second duplicate as its own rank from left to right, ie; the first #5 is the highest, the second #5 is second, #4 is the third etc.
I need a formula in F:J that would add 1 to the second and third highest numbers
F:J would look like 1,5,5,3,6

 

[philwojo]

I see what you mean now, you have to account for the 1st largest value as well, test for that first, if it is then just return that value. then do your current test and have it do what you want. I think you are very close. I have to jump to a meeting now can't help more, sorry.

phil

 

[jtakw]

Hi,

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	5	5	4	3	2	5	6	5	3	2
2	1	5	4	3	2	1	5	5	4	2
3	1	5	4	3	5	1	5	5	3	6
Sheet490

Cell Formulas
Range	Formula
F1	=IF(AND(COUNTIF($A1:$E1,A1)=COUNTIF($A1:A1,A1),OR(LARGE($A1:$E1,{2,3})=A1)),A1+1,A1)

F1 formula copied across to J1, and down if needed.

 

[JHestter]

Thanks jtakw

That's close, but that just eliminates the same number if its earlier in the sequence and does not work if there's now 3 of a number
ex;
5,5,5,2,2 turns to 5,5,6,2,2 when it should be 5,6,6,2,2

Also at times I would need to be able to change it to retrieve different number ranks, any 2 combinations of 1st, 2nd, 3rd, 4th, 5th

Thanks