Microsoft Office Forums - View Single Post - Add 100 only if greater than 5200 (condition); Keep the same value between 2 values.

rolysudest · 01-23-2023, 02:33 PM

Hello!
Can somebody help me?
I am back with the attached simplified excel table.
I have to post the text with the modified cell names once again, because I deleted many columns and rows.

I already have TWO formulas in two cells (columns), dependent on the same input cell, E7

Column 1) (result cell, J7) =IF(E7>=5200,MAX(150,ROUNDUP((E7-200)/25,-1))+100,MAX(150,ROUNDUP(E7/25,-1 )))
I would like to upgrade this formula with the following conditions:
* If 7450<E7<=8950, the result of cell J7 must remain fixed, remain the same, no longer increase proportionally, remain as at E7 = 7451.
* If E7>8950, the value of J7 will increase proportionally again, where it left off, but by +50 instead of +100. ((J7-200)/25,-1))+50

Column 2) (result cell, L7) =IF(E7>=5200,MAX(150,ROUNDUP((E7-200)/25,-1)),MAX(150,ROUNDUP(E7/25,-1)) )
I would like to upgrade this formula with the following conditions:
* If 7450<E7<=8950, to the result of cell L7 add the value that is no longer added in J7 (because its value no longer increases)
* If E7>8950, the value of L7 will increase proportionally again, where it left off, by +50. ((E7-200)/25,-1))+50

EXAMPLE;
The E7 value is 7450; The J value (Column 1) will be 390 and the L value (Column 2) will be 290
The E7 value is 7451; The value of J (Column 1) will be 400 and the value of L (Column 2) will be 300
The value of E7 is 7800; The value of J (Column 1) will be 400 and the value of L (Column 2) will be 320
The value of E7 is 8000; The value of J (Column 1) will be 400 and the value of L (Column 2) will be 340
The value of E7 is 8300; The value of J (Column 1) will be 400 and the value of L (Column 2) will be 360
The value of E7 is 8500; The value of J (Column 1) will be 400 and the value of L (Column 2) will be 380
The value of E7 is 8800; The value of J (Column 1) will be 400 and the value of L (Column 2) will be 400
The value of E7 is 8950; The value of J (Column 1) will be 400 and the value of L (Column 2) will be 400
The value of E7 is 9000; The J value (Column 1) will be 410 and the L value (Column 2) will be 410
The value of E7 is 9300; The J value (Column 1) will be 420 and the L value (Column 2) will be 420
The value of E7 is 9500; The J value (Column 1) will be 430 and the L value (Column 2) will be 430
The value of E7 is 9800; The J value (Column 1) will be 440 and the L value (Column 2) will be 440

And so on...

Thank you very much for your help!

Copie simplificata - SIMULARE INSTRUMENTISTI EGAL - Google Sheets