Hi i have been trying to get this formular to work but it keeps coming up you have entered to many arguments can anyone see what i am missing?? the top one is the origianal, bottom one is the one i am trying to get????
=IF(M3="YES",N3,ROUND(CONCATENATE(IF(AND(H3="YES",F3="Back"),G3*(E3-1)*(1-$E$1),),IF(AND(H3="YES",F3="Lay"),G3*1-$E$1),),IF(AND(H3="NO",F3="Back"),-G3,),IF(AND(H3="NO",F3="Lay"),-G3*(E3-1),),IF(AND(H3="WAIT"),0,),IF(AND(H3=""),0,)),2))
=IF(M3="YES",N3,ROUND(CONCATENATE(IF(AND(H3="YES",F3="Back"),G3*(E3-1)*(1-$E$1),),IF(AND(H3="YES",F3="Lay"),G3*1-$E$1),),IF(AND(H3="NO",F3="Back"),-G3,),IF(AND(H3="NO",F3="Lay"),-G3*(E3-1),),IF(AND(H3="WAIT"),0,),IF(AND(H3=""),0,)),2))
any help would be most welcome i cannot see it have tried it 6 time,s to night
thanks for any help.
formular doing my head in
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
Break it down into a simpler form.
Move some of those IFs to other cells so you can just reference them in the main formula.
It's important to do that because even if you get the formula syntax right you'll struggle to debug it if the result doesn't look right.
No formula should have more than a couple of IFs really, imagine trying to figure out what that was doing when you look at in a year's time.
Move some of those IFs to other cells so you can just reference them in the main formula.
It's important to do that because even if you get the formula syntax right you'll struggle to debug it if the result doesn't look right.
No formula should have more than a couple of IFs really, imagine trying to figure out what that was doing when you look at in a year's time.
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
Your last couple of ANDs have only got one value parameter.
Eg AND(h3="")
Eg AND(h3="")
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
MIght help if you said what it's meant to do then people can pinpoint quickly where the error is.
-
- Posts: 30
- Joined: Wed Feb 01, 2017 1:45 pm
Thanks for replys i have sorted it now with a bit of perseverance