UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Formula Using If Or Returning All Values, Office 2013    
 
   
halefamily104
post Apr 29 2020, 09:27 AM
Post#1



Posts: 548
Joined: 7-November 10



Good Morning UA!

I received fantastic help from UA with a formula using IF(AND. I have modified that formula to use in another spreadsheet but using OR instead of And. The reason I changed it to OR is because the search criteria has changed. Currently, using this formula:

=IF(or($C5="IN"), Summary!$D$11,""),IF(or($C5="ST"),Summary!$D$14,""),IF(or($C5="MA"),Summary!$D$17,"")))

Should return the appropriate value in the Summary sheet. The value is being returned but for all criteria, not just the one it matches. Currently as the formula stands, its giving me an error of "there is a problem with this formula" and is highlighting the Close parenthesis. I thought maybe I had too many close parenthesis or too few and changed them both. Still didn't work.

Tried this too:

=IF(ISERROR(or($C5="IN"),Summary!$E$11,IF(ISERROR(or($C5="ST"),Summary!$D$14,IF(ISERROR(or($C5="MA"),Summary!$D$17,""))))

So I want the value to be returned that matches the criteria, for example IF C5 is "IN" return the value in Summary D11, if not, return blank OR if C5 is "ST", return the value in Summary D14, if not return blank, etc.

Any help or guidance will be appreciated!

Thanks


Go to the top of the page
 
ADezii
post Apr 29 2020, 09:42 AM
Post#2



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


Wouldn't the following work, with each IF() condition being evaluated in sequence?
CODE
=IF($C5="IN",Summary!$D$11,IF($C5="ST",Summary!$D$14,IF($C5="MA",Summary!$D$17,"")))
Go to the top of the page
 
vtd
post Apr 29 2020, 09:49 AM
Post#3


Retired Moderator
Posts: 19,777
Joined: 14-July 05
From: Sydney NSW Australia


I think you need the NESTED IFs, NOT ORs Like:
CODE
=IF($C5="IN", Summary!$D$11, IF($C5="ST", Summary!$D$14, IF($C5="MA", Summary!$D$17,"")))


Check your logic since your process description is not logically consistent/sound, AFAICS.

Go to the top of the page
 
halefamily104
post Apr 29 2020, 12:00 PM
Post#4



Posts: 548
Joined: 7-November 10



Thanks so much Adezii and vtd! I modified my formula and it works great! Thanks so much!!!!

hat_tip.gif uarulez2.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 03:58 AM