2Likes
LinkBack URL
About LinkBacks
Reply With Quote=IF(OR(B1="",B1>1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
I did not take into account negative numbers but I hope you will not enter them
but if this is going to happen
=IF(OR(B1="",B1>1,B1<1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
This is a discussion on How to do a complex if and formula in excel within the Excel Questions forums, part of the Question Forums category; I want to know how to do the following in excel I've written if and loops but I haven't been ...
How to do a complex if and formula in excel
I want to know how to do the following in excel I've written if and loops but I haven't been able to figure this one out.
I have numbers in column B
If the value in column B are greater then 0 and less than or equal to .25 I want to display the number 1
If the value in column B are greater then .25 but less than or equal to .50 I want to display the number 2
If the value in column B are greater then .50 but less than or equal to .75 I want to display the number 3
If the value in column B are greater then .75 but less than or equal to 1.00 I want to display the number 4
Re: How to do a complex if and formula in excel
=IF(OR(B1="",B1>1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
I did not take into account negative numbers but I hope you will not enter them
but if this is going to happen
=IF(OR(B1="",B1>1,B1<1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
www.excelaris.net
Re: How to do a complex if and formula in excel
An alternative approach, if you are 100% certain that the values will ALWAYS be between 0 and 1.
=roundup(B1/.25,0)
The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !
Sub Macro()
ActiveCell = "IY" & Right(Application.Name, 5)
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Name = "Webdings"
.Color = 255
End With
End Sub
Re: How to do a complex if and formula in excel
You probably meant =IF(OR(B1="",B1>1,B1<0),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))Originally Posted by Robert Mika
"It is necessary; therefore, it is possible."
Re: How to do a complex if and formula in excel
Yes.
Thank you for the correction.
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
www.excelaris.net
Posting Permissions
Bookmarks