Google-sheets – Formula Parse error

formulasgoogle sheets

Trying to use this formula in Google Sheets but getting a #error! parse error. Any help as to what may be wrong in the above formula

=if(AND(B4="YES",G4=0),(ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*35%*12)),(IF(AND(C4="NO",F4>10),(ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*35*12)),(IF(F4<=10),(ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*25%*12)),0))))

The above formula is to come up with limits based on the above parameters being:

B: Existing Customer
C: Has purchased a service making him not eligible for the current one
F: Daily ctivity (Number of hours active)
G: Outstanding dues
I,K,M,O,Q,S: 6 week earnings

I tried a couple of things but the parse message still comes up every time I try and execute this.

Best Answer

I think this is what you want having worked my way through this jungle:

=IF(AND(B4="YES",G4=0),ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*35*12),IF(AND(C4="NO",F4>10),ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*35*12),IF(AND(C4="NO",F4>10),ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*35*12),IF(F4<=10,(ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*25*12)),0))))

Problem 1: You're writing 35%* and 25%* that doesn't work

Problem 2: You forgot to open a bracket for the arguments after the last IF statement

Style tip: You don't need to put arguments to formulae in brackets like AND((IF(a,b,c),(IF(a,b,c))); AND(IF(a,b,c),IF(a,b,c)) works just fine. Your formula is very difficult to decipher because of that.

Formula debugging tip: Google Sheets actually underlines errors in curly red and gives some debugging information. If you put your formula in it first complains about the 35%*, then once you fix that if will complain about the last IF() statement having 5 arguments.

Related Topic