formula help with text "yes, "no" already have formula just need it revised

steve89

Board Regular
Joined
Oct 4, 2015
Messages
152
hello i have this formula

=IFERROR(IF($BQ$24=13,IF($BL$26=0,"yes","no")&TEXT($BL$26/BL$26,),"NO"),"yes")

i want the formula to know the following because it works to an extent
if BL26 = 0 then "NO"
if BQ24 = 13 then "YES"


thank you
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
What is the relationship between BL26 and BQ24? Will they ever have a value at the same time? If yes, what happens when BL26 = 0 AND BQ24 = 13?

Otherwise, perhaps something like this:

=IF(BL24=0,"NO",IF(BQ24=13,"YES",""))
 

steve89

Board Regular
Joined
Oct 4, 2015
Messages
152
that works perfectly but can we somehow put also in that formula if BL26 equals 0 then "no"
 

steve89

Board Regular
Joined
Oct 4, 2015
Messages
152

ADVERTISEMENT

thank you i revised it with this:

=IF(BQ24=12,"yes",IF(BQ24=13,"no",""))

but the problem is the formula needs to know that also if BL27 =12 then return "NO" also is that possible to include? thanks so much again
 

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
thank you i revised it with this:

=IF(BQ24=12,"yes",IF(BQ24=13,"no",""))

but the problem is the formula needs to know that also if BL27 =12 then return "NO" also is that possible to include? thanks so much again

Ok, so back to my first question.. What is the relationship between the two cells? Should BL27 be checked first? Walk me through the logic if you can please, from the first step to the last.
 

steve89

Board Regular
Joined
Oct 4, 2015
Messages
152

ADVERTISEMENT

okay got it -- so on my worksheet there's a calendar where conditions are entered. Now this worksheet can be used for ever so if I'm non 2018 and i enter an employee had "EG/IG" that means there were evaluated and received and increase. If "EG/NI" that means an evaluation was administered but an increase to there pay rate was denied. when BQ24 =13 then cell U4 which reads "increase giving" it would say "no". Which the formula does right now correctly. When BQ24=12 then that means an increase was giving and it reads "YES" which also works correctly. But when i turn the calendar to 2017 and BL27 = 0 it should say no because previous cant use the same formula provided without the formula knowing zero on the particular call has to say "no" Hope this helps
 

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
Ok, I think I understand. How about this:

=IF(BL27=0,"no",IF(BQ24=12,"yes",IF(BQ24=13,"no","")))
 

steve89

Board Regular
Joined
Oct 4, 2015
Messages
152
thank you so much formulas keep colliding causing issues no fault to you trying to re write it -- can we incorporate if U4 = "NO" then then U5 should also = "NO"? i know its tricky im sorry i appreciate all your help.
=IF(BL27=0,"no",IF(BQ24=12,"yes",IF(BQ24=13,"no","")))
 

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
thank you so much formulas keep colliding causing issues no fault to you trying to re write it -- can we incorporate if U4 = "NO" then then U5 should also = "NO"? i know its tricky im sorry i appreciate all your help.
=IF(BL27=0,"no",IF(BQ24=12,"yes",IF(BQ24=13,"no","")))

Is this formula being placed in U5?

If so, maybe this:

=IF(OR(BL27=0,U4="no"),"no",IF(BQ24=12,"yes",IF(BQ24=13,"no","")))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top