If statement code help

[jcaptchaos2]

Hello,

I need to add more to this if statement and it is telling me to many arguments. I need to add

IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="",0

is there another way to do it.

To this

=ROUND(IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="no",IF(E28=0,0,IF(C28=0,0,MOD(L28-K28,1)-IF(F28>359,0.020833333333,0)))-H28/60/24,IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="yes",IF(E28=0,0,IF(C28=0,0,MOD(L28-K28,1)-IF(F28>359,0.020833333333,0)))-(H28/60/24)+0.0208333333333,IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="yes all",IF(E28=0,0,IF(C28=0,0,MOD(L28-K28,1)-IF(F28>359,0.020833333333,0)))-(H28/60/24))))*96-"0:48",0)/96

 

[jcaptchaos2]

The bottom code is the code I have, I need to add the top code to the bottom.

 

[seenfresh]

Typically the "too many arguments" error indicates that the structure of your if statement or functions is incorrect. Either missing a closing bracket, comma, or has too many...etc

Judging by the complexity of your formula it is very easy to do this.

The best thing to do is break your formula out into section/pieces by each function and put them back together while keeping in mind proper closing brackets... I've done something similar breaking the formula into sections which makes it a bit easier to manage.

=IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="",0,
ROUND(
IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="no",
IF(AND(E28=0,C28=0),MOD(L28-K28,1)-IF(F28>359,0.020833333333,0))-H28/60/24,
IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="yes",
IF(AND(E28=0,C28=0),MOD(L28-K28,1)-IF(F28>359,0.020833333333,0))-(H28/60/24)+0.0208333333333,
IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="yes all",
IF(AND(E28=0,C28=0),MOD(L28-K28,1)-IF(F28>359,0.020833333333,0))-(H28/60/24))))
*96-“0:48”,0)/96)

Does the above work?

Also replaced "IF(E28=0,0,IF(C28=0,0," with "IF(AND(E28=0,C28=0),"

 

[jcaptchaos2]

seenfresh thanks for your help, I am getting an error on tis formula at the end it is highlighting 48"

 

[jcaptchaos2]

Here is what I have after you advice,
[code[
=ROUND(IF(VLOOKUP(I29,R$11:S$13,2,FALSE)="no",IF(E29=0,0,IF(C29=0,0,MOD(L29-K29,1)-IF(F29>359,0.020833333333,0)))-H29/60/24,IF(VLOOKUP(I29,R$11:S$13,2,FALSE)="yes",IF(E29=0,0,IF(C29=0,0,MOD(L29-K29,1)-IF(F29>359,0.020833333333,0)))-(H29/60/24)+0.0208333333333,IF(VLOOKUP(I29,R$11:S$13,2,FALSE)="yes all",IF(E29=0,0,IF(C29=0,0,MOD(L29-K29,1)-IF(F29>359,0.020833333333,0)))-(H29/60/24))))*96-"0:48",0)/96
[/code]

 

[jcaptchaos2]

sorry about that

=ROUND(IF(VLOOKUP(I29,R$11:S$13,2,FALSE)="no",IF(E29=0,0,IF(C29=0,0,MOD(L29-K29,1)-IF(F29>359,0.020833333333,0)))-H29/60/24,IF(VLOOKUP(I29,R$11:S$13,2,FALSE)="yes",IF(E29=0,0,IF(C29=0,0,MOD(L29-K29,1)-IF(F29>359,0.020833333333,0)))-(H29/60/24)+0.0208333333333,IF(VLOOKUP(I29,R$11:S$13,2,FALSE)="yes all",IF(E29=0,0,IF(C29=0,0,MOD(L29-K29,1)-IF(F29>359,0.020833333333,0)))-(H29/60/24))))*96-"0:48",0)/96

 

[jcaptchaos2]

Ok got this to work

=IF(H28=0,IF(E28=0,0,IF(C28=0,0,MOD(L28-K28,1)-IF(F28>359,0.020833333333,0))),ROUND(IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="no",IF(E28=0,0,IF(C28=0,0,MOD(L28-K28,1)-IF(F28>359,0.020833333333,0)))-H28/60/24,IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="yes",IF(E28=0,0,IF(C28=0,0,MOD(L28-K28,1)-IF(F28>359,0.020833333333,0)))-(H28/60/24)+0.0208333333333,IF(VLOOKUP(I28,R$11:S$13,2,FALSE)="yes all",IF(E28=0,0,IF(C28=0,0,MOD(L28-K28,1)-IF(F28>359,0.020833333333,0)))-(H28/60/24))))*96-"0:48",0)/96)

 

[seenfresh]

Works now?

I often find it difficult to debug large and complex functions, find it useful copying them and pasting them into a note pad or Word and breaking out the functions and slowly putting them back together closing out the brackets as I go along... It's like a big puzzle sometimes LOL

 

[scottylad2]

My last post failed, the part at the start of your formula

IF(H28=0,IF(E28=0,0,IF(C28=0,0</pre>
is it a case of any of those cells mentioned are a 0 then 0 should be returned? if thats the case then you can reduce by a couple of IF statements if you wrap this first part in an Or statement


IF(OR(H28=0,E28=0,C28=0),0</pre>
and if its a case of all three cells need to show 0 to return a 0 then replace the Or with an And.

Excel restricts the number of IF statements you can have nested to a max of 7 which is most likely where your error is stemming from

 

[jcaptchaos2]

Thanks for your help