Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-31-2017, 05:06 PM
Brittni Brittni is offline IF AND formula: replacing the FALSE Value with 0 when nothing entered Windows 7 32bit IF AND formula: replacing the FALSE Value with 0 when nothing entered Office 2010 32bit
Novice
IF AND formula: replacing the FALSE Value with 0 when nothing entered
 
Join Date: Jan 2017
Posts: 2
Brittni is on a distinguished road
Default IF AND formula: replacing the FALSE Value with 0 when nothing entered

I am working with the current formula but instead of having a FALSE value I would like the cell to be left blank until information is added to the spreadsheet.



=IF(AND(K2>115%,K2<1000%),"5",IF(AND(K2>105%,K2<11 5%),"4",IF(AND(K2>95%,K2<105%),"3",IF(AND(K2>85%,K 2<95%),"2",IF(AND(K2>0%,K2<85%),"1")))))
Reply With Quote
  #2  
Old 01-31-2017, 08:17 PM
jeffreybrown jeffreybrown is offline IF AND formula: replacing the FALSE Value with 0 when nothing entered Windows Vista IF AND formula: replacing the FALSE Value with 0 when nothing entered Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown 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
  #3  
Old 02-01-2017, 12:42 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF AND formula: replacing the FALSE Value with 0 when nothing entered Windows 7 64bit IF AND formula: replacing the FALSE Value with 0 when nothing entered Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

BTW is there any reason why the different results of the formula should be text ? "5" is a text string while 5 is a number
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
IF AND formula: replacing the FALSE Value with 0 when nothing entered Trying to create a formula for descriptions to be added if a code is entered rthomas Excel 6 10-24-2016 06:39 AM
IF AND formula: replacing the FALSE Value with 0 when nothing entered Formula that will use different rates depending on the value entered zsteve Excel 4 08-11-2016 09:16 AM
IF AND formula: replacing the FALSE Value with 0 when nothing entered Embedded SWF - 'Playing' is false after run Cosmo PowerPoint 14 11-03-2013 01:21 AM
IF AND formula: replacing the FALSE Value with 0 when nothing entered False fonts in word peterv Word 5 07-19-2012 02:11 AM
IF AND formula: replacing the FALSE Value with 0 when nothing entered Formula to select description when code is entered Natasha Excel 1 09-25-2011 12:59 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:34 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft