software

Can someone tell me why this formula gives me an error message? It works for
0 thru 5 but when i add the sixth or more it errors out.
Thanks, John

=IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))

There is a limit of 7 nested if functions and you have reached it.

quot;Inspectorquot; wrote:

gt; Can someone tell me why this formula gives me an error message? It works for
gt; 0 thru 5 but when i add the sixth or more it errors out.
gt; Thanks, John
gt;
gt; =IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
gt; IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
gt; IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
gt; IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
gt; IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
gt; IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
gt; IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))

maybe

=IF(AND(COUNTBLANK(F3:O3)gt;3,COUNTBLANK(F3:O3)lt;8),T RUNC(SUM(F3:O3)/(10*(10-COUNTBLANK(F3:O3)))),IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),quot;quot;))))quot;Inspectorquot; wrote:

gt; Can someone tell me why this formula gives me an error message? It works for
gt; 0 thru 5 but when i add the sixth or more it errors out.
gt; Thanks, John
gt;
gt; =IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
gt; IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
gt; IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
gt; IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
gt; IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
gt; IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
gt; IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))

I actually have to continue up to IF 10-COUNTBLANK =10

quot;Duke Careyquot; wrote:

gt; maybe
gt;
gt; =IF(AND(COUNTBLANK(F3:O3)gt;3,COUNTBLANK(F3:O3)lt;8),T RUNC(SUM(F3:O3)/(10*(10-COUNTBLANK(F3:O3)))),IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),quot;quot;))))
gt;
gt;
gt; quot;Inspectorquot; wrote:
gt;
gt; gt; Can someone tell me why this formula gives me an error message? It works for
gt; gt; 0 thru 5 but when i add the sixth or more it errors out.
gt; gt; Thanks, John
gt; gt;
gt; gt; =IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
gt; gt; IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
gt; gt; IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
gt; gt; IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
gt; gt; IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
gt; gt; IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
gt; gt; IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))

If the formulas for 7 to 10 follow those of 3 to 6 then Duke's solution would
work with a minor modification.

What are those for 7 to 10?

quot;Inspectorquot; wrote:

gt; I actually have to continue up to IF 10-COUNTBLANK =10
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; maybe
gt; gt;
gt; gt; =IF(AND(COUNTBLANK(F3:O3)gt;3,COUNTBLANK(F3:O3)lt;8),T RUNC(SUM(F3:O3)/(10*(10-COUNTBLANK(F3:O3)))),IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),quot;quot;))))
gt; gt;
gt; gt;
gt; gt; quot;Inspectorquot; wrote:
gt; gt;
gt; gt; gt; Can someone tell me why this formula gives me an error message? It works for
gt; gt; gt; 0 thru 5 but when i add the sixth or more it errors out.
gt; gt; gt; Thanks, John
gt; gt; gt;
gt; gt; gt; =IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))

Each cell in F3:O3 represents a total of 10 games of bowling/day. F3:J3
would be 5 days or 50 games for the month of Nov. K3:O3 would be 5 days
or 50 games in Dec. All these games may or may not be bowled. I'm good for
the first 5 IF's but just need to continue it to 10 IF's, F3:O3.

quot;Toppersquot; wrote:

gt; If the formulas for 7 to 10 follow those of 3 to 6 then Duke's solution would
gt; work with a minor modification.
gt;
gt; What are those for 7 to 10?
gt;
gt; quot;Inspectorquot; wrote:
gt;
gt; gt; I actually have to continue up to IF 10-COUNTBLANK =10
gt; gt;
gt; gt; quot;Duke Careyquot; wrote:
gt; gt;
gt; gt; gt; maybe
gt; gt; gt;
gt; gt; gt; =IF(AND(COUNTBLANK(F3:O3)gt;3,COUNTBLANK(F3:O3)lt;8),T RUNC(SUM(F3:O3)/(10*(10-COUNTBLANK(F3:O3)))),IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),quot;quot;))))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Inspectorquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Can someone tell me why this formula gives me an error message? It works for
gt; gt; gt; gt; 0 thru 5 but when i add the sixth or more it errors out.
gt; gt; gt; gt; Thanks, John
gt; gt; gt; gt;
gt; gt; gt; gt; =IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))

what i wanted to know was:

IF(10-COUNTBLANK(F3:O3)=7,TRUNC(SUM(F3:O3)/70?
IF(10-COUNTBLANK(F3:O3)=8,TRUNC(SUM(F3:O3)/80?
?
?

quot;Inspectorquot; wrote:

gt; Each cell in F3:O3 represents a total of 10 games of bowling/day. F3:J3
gt; would be 5 days or 50 games for the month of Nov. K3:O3 would be 5 days
gt; or 50 games in Dec. All these games may or may not be bowled. I'm good for
gt; the first 5 IF's but just need to continue it to 10 IF's, F3:O3.
gt;
gt; quot;Toppersquot; wrote:
gt;
gt; gt; If the formulas for 7 to 10 follow those of 3 to 6 then Duke's solution would
gt; gt; work with a minor modification.
gt; gt;
gt; gt; What are those for 7 to 10?
gt; gt;
gt; gt; quot;Inspectorquot; wrote:
gt; gt;
gt; gt; gt; I actually have to continue up to IF 10-COUNTBLANK =10
gt; gt; gt;
gt; gt; gt; quot;Duke Careyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; maybe
gt; gt; gt; gt;
gt; gt; gt; gt; =IF(AND(COUNTBLANK(F3:O3)gt;3,COUNTBLANK(F3:O3)lt;8),T RUNC(SUM(F3:O3)/(10*(10-COUNTBLANK(F3:O3)))),IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),quot;quot;))))
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Inspectorquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Can someone tell me why this formula gives me an error message? It works for
gt; gt; gt; gt; gt; 0 thru 5 but when i add the sixth or more it errors out.
gt; gt; gt; gt; gt; Thanks, John
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))

Hi

=TRUNC((SUM(F3:O3) CHOOSE(11-COUNTBLANK(F3:O3),C3,C3*20,C3*10,0,0,0,0,0,0,0,0))/CHOOSE(11-COUNTBLANK(F3:O3),1,30,30,30,40,50,60,70,80,90,100 ))

(There can be up to 28 options in CHOOSE function)--
Arvi Laanemets
( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )quot;Inspectorquot; gt; wrote in message
news
gt; Can someone tell me why this formula gives me an error message? It works
gt; for
gt; 0 thru 5 but when i add the sixth or more it errors out.
gt; Thanks, John
gt;
gt; =IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
gt; IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
gt; IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
gt; IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
gt; IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
gt; IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
gt; IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))
Yes, that is correct

quot;Toppersquot; wrote:

gt; what i wanted to know was:
gt;
gt; IF(10-COUNTBLANK(F3:O3)=7,TRUNC(SUM(F3:O3)/70?
gt; IF(10-COUNTBLANK(F3:O3)=8,TRUNC(SUM(F3:O3)/80?
gt; ?
gt; ?
gt;
gt; quot;Inspectorquot; wrote:
gt;
gt; gt; Each cell in F3:O3 represents a total of 10 games of bowling/day. F3:J3
gt; gt; would be 5 days or 50 games for the month of Nov. K3:O3 would be 5 days
gt; gt; or 50 games in Dec. All these games may or may not be bowled. I'm good for
gt; gt; the first 5 IF's but just need to continue it to 10 IF's, F3:O3.
gt; gt;
gt; gt; quot;Toppersquot; wrote:
gt; gt;
gt; gt; gt; If the formulas for 7 to 10 follow those of 3 to 6 then Duke's solution would
gt; gt; gt; work with a minor modification.
gt; gt; gt;
gt; gt; gt; What are those for 7 to 10?
gt; gt; gt;
gt; gt; gt; quot;Inspectorquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I actually have to continue up to IF 10-COUNTBLANK =10
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Duke Careyquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; maybe
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =IF(AND(COUNTBLANK(F3:O3)gt;3,COUNTBLANK(F3:O3)lt;8),T RUNC(SUM(F3:O3)/(10*(10-COUNTBLANK(F3:O3)))),IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),quot;quot;))))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Inspectorquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Can someone tell me why this formula gives me an error message? It works for
gt; gt; gt; gt; gt; gt; 0 thru 5 but when i add the sixth or more it errors out.
gt; gt; gt; gt; gt; gt; Thanks, John
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
gt; gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
gt; gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
gt; gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
gt; gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
gt; gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
gt; gt; gt; gt; gt; gt; IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))

thank you

quot;Arvi Laanemetsquot; wrote:

gt; Hi
gt;
gt; =TRUNC((SUM(F3:O3) CHOOSE(11-COUNTBLANK(F3:O3),C3,C3*20,C3*10,0,0,0,0,0,0,0,0))/CHOOSE(11-COUNTBLANK(F3:O3),1,30,30,30,40,50,60,70,80,90,100 ))
gt;
gt; (There can be up to 28 options in CHOOSE function)
gt;
gt;
gt; --
gt; Arvi Laanemets
gt; ( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
gt;
gt;
gt; quot;Inspectorquot; gt; wrote in message
gt; news
gt; gt; Can someone tell me why this formula gives me an error message? It works
gt; gt; for
gt; gt; 0 thru 5 but when i add the sixth or more it errors out.
gt; gt; Thanks, John
gt; gt;
gt; gt; =IF(10-COUNTBLANK(F3:O3)=0,TRUNC(SUM(F3:O3) C3),
gt; gt; IF(10-COUNTBLANK(F3:O3)=1,TRUNC((SUM(F3:O3) C3*20)/30),
gt; gt; IF(10-COUNTBLANK(F3:O3)=2,TRUNC((SUM(F3:O3) C3*10)/30),
gt; gt; IF(10-COUNTBLANK(F3:O3)=3,TRUNC(SUM(F3:O3)/30),
gt; gt; IF(10-COUNTBLANK(F3:O3)=4,TRUNC(SUM(F3:O3)/40),
gt; gt; IF(10-COUNTBLANK(F3:O3)=5,TRUNC(SUM(F3:O3)/50),
gt; gt; IF(10-COUNTBLANK(F3:O3)=6,TRUNC(SUM(F3:O3)/60,quot;quot;))))))
gt;
gt;
gt;