Help with a logical formula

[MillieG]

Hello All,

I'm not very good with Excel at all and I need help in creating a logical formula with more than one value. I appreciate any help Thank you !

It goes like this:

If anything in E2 through E101 is "1", and anything in F2 through F102 is 'JR", and anything in G2 through G102 is "Bricklayer" then I want the amount of $60.18 to appear in columns C2 through C101 as it applies.


Columns C2-C101 = Base Rate
Columns E2-E102 = Union Local
Columns F2-F102 = JR
Columns G2-G101 = Bricklayer

 

[Scott Huish]

=IF(AND(E2=1,F2="JR",G2="Bricklayer"),60.18,"")

Copy down.

 

[mrhstn]

I suppose instead of and you could just concatenate and test against a single string...

=IF(E2&F2&G2="1JRBricklayer",60.18,"")

 

[Scott Huish]

I suppose you could, but you'd want to be careful with that because it doesn't indicate which cells each piece of data comes from. I would just use AND but to do it your way I would delimit it.

For example if E2 was 1J, F2 was R, and G2 was Bricklayer, your formula would return 60.18 even though it doesn't match the requirements. Not saying that the op has this necessarily but it is something to consider.
=IF(E2&","&F2&","&G2="1,JR,Bricklayer",60.18,"")

 

[MillieG]

Thank you both ! I've tried it and got nothing. Maybe it's me. I'll keep you posted.

 

[mrhstn]

I see the thing with delimiting...another option as there is a numeric value being return is some boolean logic...

=(E2=1)*(F2="JR")*(G2="Bricklayer")*60.18

It would return 0 if the conditions aren't met, this could be formatted to leave a blank if desired.

 

[MillieG]

Yes, this formula, =(E2=1)*(F2="JR")*(G2="Bricklayer")*60.18 has returned 0. I'm not getting the desired outcome with anything I try.

Will numbers stored as text interfere with the formula ?

 

[mrhstn]

It could very well do, if you have numbers stored as text then apply some coercion to the equation.

=(E2+0=1)*(F2="JR")*(G2="Bricklayer")*60.18

In the above if E2 has 1 as text then it'll coerce it to a numeric value to test.

 

[MillieG]

Sigh...No luck. I can't understand what went wrong.

Thank you so much for your help.

 

[mrhstn]

Maybe some trailing spaces/characters in the cells. Couple of things to try.

1. Split the logical tests into individual columns, then you can look to see (for example) if the cell contains 'Bricklayer' but =G2="Bricklayer" returns FALSE.
2. Hit the logical tests with some trimming and cleaning...

=(TRIM(CLEAN(E2))+0=1)*(TRIM(CLEAN(F2))="JR")*(TRIM(CLEAN(G2))="Bricklayer")*60.18