Microsoft Office Forums Add 100 only if greater than 5200 (condition); Keep the same value between 2 values.
 Register FAQ Search Today's Posts Mark Forums Read

#1
01-23-2023, 02:33 PM
 rolysudest Mac OS X Office 2016 Novice Join Date: Jan 2023 Posts: 1
Add 100 only if greater than 5200 (condition); Keep the same value between 2 values.

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

Last edited by rolysudest; 01-24-2023 at 08:17 AM. Reason: I deleted many columns and rows from my excel table.
#2
01-24-2023, 02:06 AM
 Pecoflyer Windows 10 Office 2021 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,667

Cross posted at Add 100 only if greater than 5200 (condition); Keep the same value between 2 values.
and
Add 100 only if greater than 5200 (condition); Keep the same until a value 2. | MrExcel Message Board

Please add links to ALL cross posts ( as text if needed) and read A message to forum cross posters - Excelguru to understand why. Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Kubi Excel 2 02-19-2018 01:45 PM LearnerExcel Excel 3 12-15-2016 03:10 AM LearnerExcel Excel 6 11-28-2016 09:45 PM bbxrider Outlook 0 09-25-2010 02:37 PM brians Excel 5 03-09-2010 12:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:55 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top