Microsoft Office Forums - View Single Post - IF AND formula: replacing the FALSE Value with 0 when nothing entered

Thread: IF AND formula: replacing the FALSE Value with 0 when nothing entered

View Single Post

Old

01-31-2017, 08:17 PM

jeffreybrown jeffreybrown is offline

jeffreybrown is offline

jeffreybrown uses Windows Vista

Windows Vista jeffreybrown uses Office 2007

jeffreybrown uses Office 2007

Office 2007

Expert

Join Date: Apr 2016

Posts: 673

jeffreybrown has a spectacular aura about

jeffreybrown has a spectacular aura about

Default

How about try...

=IF(H2<>"",IF(AND(H2>115%,H2<1000%),"5",IF(AND(H2>105%,H2<115 %),"4",IF(AND(H2>95%,H2<105%),"3",IF(AND(H2>85%,H2 <95%),"2",IF(AND(H2>0%,H2<85%),"1"))))),"")

but this seems to work better...

=IF(H2<>"",LOOKUP(H2,{0,0.85,0.95,1.05,1.15},{1,2, 3,4,5}),"")

Reply With Quote