IF formula with many greater then, and less then conditions

**age** · 02-23-2023, 12:55 PM

Hi,

I'm struggling with this formula and cannot figure this out:

=IF(G18/2<2;0);IF(G18/2>=2;<3;2);IF(G18/2>=3;<4;3);IF(G18/2>=4;<5;4);IF(G18/2>=5;>6;5);IF(G18/2>=6;<7;6);IF(G18/2>=7;7)))))))

Basically it it would return the answer based on the number in cell G18 divided by 2, based on:

If G18/2 is greater than= 7 and above, 7 is the answer
If G18/2 is greater than= 6 and less then 7, 6 is the answer
If G18/2 is greater than= 5 and less then 6, 5 is the answer
If G18/2 is greater than= 4 and less then 5, 4 is the answer
If G18/2 is greater than= 3 and less then 4, 3 is the answer
If G18/2 is greater than= 2 and less then 3, 2 is the answer
If G18/2 is less than 2, 0 is the answer

Hope somebody could help me out, thanks.

**Pete_UK** · 02-23-2023, 01:07 PM

Try this:

=MIN(7,INT(G18/2))

Hope this helps.

Pete

**FlameRetired** · 02-23-2023, 03:50 PM

Another way:

Formula:

Please Login or Register  to view this content.

**age** · 02-24-2023, 06:22 AM

Wow thank you so much, that worked out brilliantly. That was above my head so now I must figure out how these two formulas work

Lookup work great, so did Min but give wrong result below 4 ex. 7/2=3,5 then it gives 1 as a result, but should be 0.

But thanks both so much!

**Pete_UK** · 02-24-2023, 02:17 PM

A slight amendment to my formula, then:

=MIN(7,INT(G18/2))*(G18>=4)

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

**FlameRetired** · 02-24-2023, 03:18 PM

By the way age welcome to the forum AND
please update your profile.

If you are not familiar with how to update your profile find the dark blue menu at the top of this page that says

Forum Actions

. Click that and 'Edit Profile'.

Thank you ahead of time.

IF formula with many greater then, and less then conditions

LinkBack

Thread Tools

Rate This Thread

Display

IF formula with many greater then, and less then conditions

Re: IF formula with many greater then, and less then conditions

Re: IF formula with many greater then, and less then conditions

Re: IF formula with many greater then, and less then conditions

Re: IF formula with many greater then, and less then conditions

Re: IF formula with many greater then, and less then conditions

Thread Information

Users Browsing this Thread

Similar Threads

Greater than and conditions doesnt seem to work

[SOLVED] Formula to check if one vale is greater than the other and sum the greater values to match

[SOLVED] Add an amount to a cell if the value of one of the conditions is greater than X

[SOLVED] Select Only If No Other Cell is Greater Than X and All Other Conditions are Met

running total with conditions H3 = H2 + G3 if H2 greater than 0 etc.

[SOLVED] IF Function with greater than 51 conditions?

Coding for comparing sections of columns for greater than/less than conditions

Bookmarks

Bookmarks

Posting Permissions