1

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.

  • 1
    Please show your data (link to sheet?) – user55949 Sep 26 at 9:26
  • A description what the various columns mean isn't actually as relevant as the data type which I assume is B:YES/NO, C:YES/NO, F-S: Numerical. Would be great if you could add that information. – Cornelius Roemer Sep 26 at 14:14
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.