Multiple IF and Greater And Less in one formula

AlexIK

New Member
Joined
Dec 14, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
First of all I am pretty new to xls formulas so sorry if my question would sound too easy for someone.
So what I would like to do is the formula with multiple IF and greater/less.
to put it simple 0~7 = 0%
8~9 = 50%
10~19 = 73%
20~49 = 81%
50~99 = 87%
100~299 = 89%
300~ = 91%
Right now I am doing input manually and looking for a help with the formula
 

Attachments

  • Formula Help.jpg
    Formula Help.jpg
    58.9 KB · Views: 24

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Try This

on Cell C1
Excel Formula:
=IF(B1>300,"91%",IF(B1>100,"89%",IF(B1>50,"87%",IF(B1>20,"81%",IF(B1>10,"73%",IF(B1>7,"50%",IF(B1>0,"0%")))))))

1702614607391.png
 
Upvote 0
Solution
Here are some additional ideas. Note that this closes up the gaps between each tier, so the first tier is 0<=x<8, second tier 8<=x<10, and so on:
Book2
ABCD
1A10%0%
2B850%50%
3C1573%73%
4D4581%81%
5E7087%87%
6F15089%89%
7H50091%91%
Sheet1
Cell Formulas
RangeFormula
C1:C7C1=XLOOKUP(B1,{0,8,10,20,50,100,300},{0,50,73,81,87,89,91}/100,,-1,1)
D1:D7D1=MAX({0,50,73,81,87,89,91}/100*(B1>={0,8,10,20,50,100,300})*(B1<{8,10,20,50,100,300,9.99E+307}))
 
Last edited:
Upvote 0
Same idea as @KRice, but I have interpreted your question differently:

ABCDEF
1MyValuesMyTable
210%00%
3950%850%
41573%1073%
54581%2081%
67087%5087%
715089%10089%
850091%30091%
9
Sheet3
Cell Formulas
RangeFormula
B2:B8B2=VLOOKUP(A2,MyTable,2)
Named Ranges
NameRefers ToCells
MyTable=Sheet3!$E$2:$F$8B2:B8
 
Upvote 0
Thank you everyone. Didnt even know it could be done in so many different ways. You guys made my day today!
 
Upvote 0
We’re happy to help…and welcome to the MrExcel Board.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top