Finding Min/Max and subtracting or adding based on condition

Hi Everyone,
Hoping someone can help me out with this. I have a string of values such as the below:

12 1 2 2 3 2 2 1 13

In my template the first value is always the number you hard code in and the rest are formulas which refer to percentages from another sheet and disperse the ttl amount of units. Because this reads off of another page of percentages the last number (due to rounding) usually is not the same as the first number you enter. Normally, I would go back take one unit away from the greatest value (aka 3) and make it a 2......now the first number and last number equal 12.

12 1 2 2 2 2 2 1 12

I want to add a formula which will identify FIRST if the first and last number equal each other. NEXT, if the first number is less than the second number I want to subtract 1 unit from the highest value (MAX) in the sequence (aka the 3 above was changed to a 2). Also, if the first number is more than the second number I want to add one unit to the 4th value in. LAST, I want the last number to be updated to the new total units.

I was thinking I could maybe use a nested if/min/max/replace formula but I keep getting stuck. Can anyone help me out with this?? Thanks so much!!

The strings are populated from a vlookup to the prior sheet with percentages on it. Nothing really complex.....do you still want to see the whole file? Thanks!!

Please see the attached

Thank you for helping! I was using a similiar formula as well. Two issues arise with the formula you used.....3 may not always be the highest number in the string. Many times this number can go up to 10. Additionally, in many cases there are multiple 3's in the string or multiples of the highest value. In an ideal world I would like to subtract from the first greatest value. Any further ideas?


Thanks again!!

This looks awesome!!!!!!!!!!!!!!!! So just to verify.....you can now enter any number and if the first number is lower than the last it will subtract from the first largest value (even if there are multiples of the largest value). Also, is there anyway we can make sure when there are units subtracted it never will fall to a 0?

Right that was the only instance i can think of that occuring as well. Is there any way in that specific occasion we can simply not drop anything? I want minimum values of 1 at all times. If not no big deal just wanted to hear your thoughts. Your're great at excel what did you study?

I'm not sure why it's not coming out correctly. Would you be able to help once more?

I've tried to re-create the same thing on my read page as I did on my worksheet tab. The formulas are starting to stump me though. I used the same formula but now its not pulling in the numbers. Would you be able to take a look at the attachment?

I'm confused......so on my attachment I am looking at X20 on the "read" sheet tab. I'm trying to correct this formula bc you wanted to see everything on one page. The correct formula already exists on the "worksheet" tab E9-K9 but i do not understand why it is returning N/A's in X20 on the "read sheet" tab. Would you help me correct this formula? I have gone over it many times and I'm confused.



Thanks again!

Fixed it!! Please see the attached now.....all formulas are on one sheet but the new min/max formula is not correcting the initial paste. Let me know your thoughts.


Thanks!!

Thanks again!
Would I be able to copy the first whole line ( Ex: from W20 to AE 20) and then paste special formulas to the lines below? I need the formula to cover many more lines.

Also in col AG why is there the word FALSE with two 3's underneath it?

fill handle? I usually just copy and paste special formulas.....whats the fill handle?

In column AG why does it say false and two number 3's underneath it? It doesn't look like anything is reading off of this. Should i leave this there?