Trying to do IF THEN Formula with >=/<= with multiple ranges

SamMold

New Member
Joined
Jan 16, 2022
Messages
2
Platform
  1. Web
Please help!

I am trying to create an IF formula using multiple ranges ranging from 0-8=.5, 9-16=1, 17-24=1.5, etc. and I am having a terribly hard time figuring out how to write it. I use google sheets for excel and would greatly appreciate some insight as to how I can create a formula that actually works. Currently, the formula I tried is "=IFS((C3>=0,C3<=8),0.05,IF(C3>=9,C3<=16),1,IF(C3>=17,C3<=24),1.5,IF(C3>=25,C3<=32),2,IF(C3>=33,C3<=40),2.5,IF(C3>=41,C3<=45),2.75,IF(C3>=46,C3<=49),3)". I've tried multiple tweaks to it but haven't had any success. If you have any insight, please help!

Thank you
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
How about
Excel Formula:
=IF(C3<=8,0.05,IF(C3<=16,1,IF(C3<=24,1.5,IF(C3<=32,2,IF(C3<=40,2.5,IF(C3<=45,2.75,IF(C3<=49,3,"")))))))
In future when asking for a Google Sheets formula please post in the General Discussion & Other Applications section of the board & make it clear that it's for sheets.
Thanks
 
Upvote 0
How about
Excel Formula:
=IF(C3<=8,0.05,IF(C3<=16,1,IF(C3<=24,1.5,IF(C3<=32,2,IF(C3<=40,2.5,IF(C3<=45,2.75,IF(C3<=49,3,"")))))))
In future when asking for a Google Sheets formula please post in the General Discussion & Other Applications section of the board & make it clear that it's for sheets.
Thanks
Thank you, sorry I didn't know I had to post it to a certain area. The formula did work. Thank you so much for your help!
 
Upvote 0
Hi,

Also try this, not sure if it works in Google sheets:

Book3.xlsx
CD
300.05
480.05
591
6181.5
7322
8332.5
9442.75
10483
11493
1251Out of range
Sheet942
Cell Formulas
RangeFormula
D3:D12D3=IF(C3="","",LOOKUP(C3,{0,9,17,25,33,41,46,50},{0.05,1,1.5,2,2.5,2.75,3,"Out of range"}))
 
Upvote 0

Forum statistics

Threads
1,226,739
Messages
6,192,739
Members
453,754
Latest member
milestogo

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