More than 7 nested if statement

  • Thread starter Thread starter Roe
  • Start date Start date
R

Roe

Hi

I have a problem with my presentation sheet for our
marching band championships.

I am referencing the Summary Sheet by category by band to
place in the presentation sheet band placings (1st) and
want to formula to populate the presentation sheet with
the bands name that has won 1st place with the trophy to
be awarded in the next column. Unfortunately I have 9
bands competing

This is my formula
=IF('S1'!$F$23=1,DATA!$C2,IF('S1'!$G$23=1,DATA!$C3,IF('S1'!
$H$23=1,DATA!$C4,IF('S1'!$I$23=1,DATA!$C5,IF('S1'!
$J$23=1,DATA!$C6,IF('S1'!$K$23=1,DATA!$C7,IF('S1'!
$L$23=1,DATA!$C8,IF('S1'!$M$23=1,DATA!$C9,IF('S1'!
$N$23=1,DATA!$C10,IF('S1'!$O$23=1,DATA!$C11))))))))))

Excel limit on 7 if statement how can I change this to
include 10.

Kind regards
Roe
 
I am not sure how your data is laid out... but have you looked at using the
CHOOSE() function? Also, it may help to post a row or two of your data you
are summarizing.
 
This is from the Summary (S1) worksheet

Catagory/ Band1 Band2 Band3-Band9
Turnout
PROGRAMME ANALYSIS 100 98 70
PERFORMANCE EFFECT 150 100 60
TOTAL 250 250 198 130
Band Placing 1 2 3

This is the presentation worksheet

Category Band Placings Points Trophy
Turnout [formula 1st formula Trophy name
to reference to ref
band from(S1)which from (S1)TOTAL]
has 1st place]

Hope this give you an idea.
 
Tried this I am unable to get this to work
-----Original Message-----
try this idea
=INDEX(c1:c21,MATCH(MAX(g1:g21),g1:g21,0))

--
Don Guillett
SalesAid Software
(e-mail address removed)



.
Click to expand...
 
I did test and it worked fine to find the largest (MAX). Maybe you want the
smallest (MIN). And, of course, you had to add the sheet names and modify
for your ranges to get it to work.
What did you try? Post your formula.
What happened ?

If all else fails, send to my PERSONAL email below a SMALL workbook and I
will look. Be sure to copy this email so I will know what I am getting and
what to do with it. Reading minds is not one of my talents. I do admit it
would come in handy around here. <BFG>
 
Sorry Don I think I did not explain myself correctly.

Below is a example of 3 worksheets, The S1 sheet holds the
scores given to each band in the competition and the S1
sheet automatically calculates the band placings eg 1st,
2nd etc.

This is from the Summary (S1) worksheet

Catagory/ Band1 Band2 Band3...Band10
Turnout
PROGRAMME ANALYSIS 100 98 70
PERFORMANCE EFFECT 150 100 60
TOTAL 250 250 198 130
Band Placing 1 2 3

This is the presentation worksheet which contains formula
to populate the bands name, who has 1st place with the
against the Trophy list for each category.

Category Band Placings Trophy
Turnout [IF F23=1] 1st Trophy 1
in worksheet S1
then goto DATA
C2 populate
with band name
IF G23 = 1
then goto DATA C3
and populate with band name



This is my formula in the Presentation Sheet. Because we
have more than 7 bands taking part in this Division the
formula will not work if I have more than 7 statements. I
have 10 bands in this Division. I was wondering if there
is a way around this.
=IF('S1'!$F$23=1,DATA!$C2,IF('S1'!$G$23=1,DATA!$C3,IF('S1'!
$H$23=1,DATA!$C4,IF('S1'!$I$23=1,DATA!$C5,IF('S1'!
$J$23=1,DATA!$C6,IF('S1'!$K$23=1,DATA!$C7,IF('S1'!
$L$23=1,DATA!$C8,IF('S1'!$M$23=1,DATA!$C9,IF('S1'!
$N$23=1,DATA!$C10,IF('S1'!$O$23=1,DATA!$C11))))))))))

The DATA sheet holds a list of Bands competing in the
Division.eg
Judges Event Band Category
[DATA C2]

S1 worksheet F23 contains the Band Placing (1st, 2nd etc)
DATA worksheet C2..C11 contains the Bands Name

Hope this give you an idea.
 
Send me a SMALL workbook and I will have a look.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Roe said:
Sorry Don I think I did not explain myself correctly.

Below is a example of 3 worksheets, The S1 sheet holds the
scores given to each band in the competition and the S1
sheet automatically calculates the band placings eg 1st,
2nd etc.

This is from the Summary (S1) worksheet

Catagory/ Band1 Band2 Band3...Band10
Turnout
PROGRAMME ANALYSIS 100 98 70
PERFORMANCE EFFECT 150 100 60
TOTAL 250 250 198 130
Band Placing 1 2 3

This is the presentation worksheet which contains formula
to populate the bands name, who has 1st place with the
against the Trophy list for each category.

Category Band Placings Trophy
Turnout [IF F23=1] 1st Trophy 1
in worksheet S1
then goto DATA
C2 populate
with band name
IF G23 = 1
then goto DATA C3
and populate with band name



This is my formula in the Presentation Sheet. Because we
have more than 7 bands taking part in this Division the
formula will not work if I have more than 7 statements. I
have 10 bands in this Division. I was wondering if there
is a way around this.
=IF('S1'!$F$23=1,DATA!$C2,IF('S1'!$G$23=1,DATA!$C3,IF('S1'!
$H$23=1,DATA!$C4,IF('S1'!$I$23=1,DATA!$C5,IF('S1'!
$J$23=1,DATA!$C6,IF('S1'!$K$23=1,DATA!$C7,IF('S1'!
$L$23=1,DATA!$C8,IF('S1'!$M$23=1,DATA!$C9,IF('S1'!
$N$23=1,DATA!$C10,IF('S1'!$O$23=1,DATA!$C11))))))))))

The DATA sheet holds a list of Bands competing in the
Division.eg
Judges Event Band Category
[DATA C2]

S1 worksheet F23 contains the Band Placing (1st, 2nd etc)
DATA worksheet C2..C11 contains the Bands Name

Hope this give you an idea.
-----Original Message-----
I did test and it worked fine to find the largest (MAX). Maybe you want the
smallest (MIN). And, of course, you had to add the sheet names and modify
for your ranges to get it to work.
What did you try? Post your formula.
What happened ?

If all else fails, send to my PERSONAL email below a SMALL workbook and I
will look. Be sure to copy this email so I will know what I am getting and
what to do with it. Reading minds is not one of my talents. I do admit it
would come in handy around here. <BFG>

--
Don Guillett
SalesAid Software
(e-mail address removed)



.
Click to expand...
Click to expand...
 
Sent email with small spreadsheet attachment, to the email
address below.

Thanks Don
-----Original Message-----
Send me a SMALL workbook and I will have a look.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Sorry Don I think I did not explain myself correctly.

Below is a example of 3 worksheets, The S1 sheet holds the
scores given to each band in the competition and the S1
sheet automatically calculates the band placings eg 1st,
2nd etc.

This is from the Summary (S1) worksheet

Catagory/ Band1 Band2 Band3...Band10
Turnout
PROGRAMME ANALYSIS 100 98 70
PERFORMANCE EFFECT 150 100 60
TOTAL 250 250 198 130
Band Placing 1 2 3

This is the presentation worksheet which contains formula
to populate the bands name, who has 1st place with the
against the Trophy list for each category.

Category Band Placings Trophy
Turnout [IF F23=1] 1st Trophy 1
in worksheet S1
then goto DATA
C2 populate
with band name
IF G23 = 1
then goto DATA C3
and populate with band name



This is my formula in the Presentation Sheet. Because we
have more than 7 bands taking part in this Division the
formula will not work if I have more than 7 statements. I
have 10 bands in this Division. I was wondering if there
is a way around this.
=IF('S1'!$F$23=1,DATA!$C2,IF('S1'!$G$23=1,DATA!$C3,IF ('S1'!
$H$23=1,DATA!$C4,IF('S1'!$I$23=1,DATA!$C5,IF('S1'!
$J$23=1,DATA!$C6,IF('S1'!$K$23=1,DATA!$C7,IF('S1'!
$L$23=1,DATA!$C8,IF('S1'!$M$23=1,DATA!$C9,IF('S1'!
$N$23=1,DATA!$C10,IF('S1'!$O$23=1,DATA!$C11))))))))))

The DATA sheet holds a list of Bands competing in the
Division.eg
Judges Event Band Category
[DATA C2]

S1 worksheet F23 contains the Band Placing (1st, 2nd etc)
DATA worksheet C2..C11 contains the Bands Name

Hope this give you an idea.
-----Original Message-----
I did test and it worked fine to find the largest
Click to expand...
(MAX).
Maybe you want the
smallest (MIN). And, of course, you had to add the
Click to expand...
sheet
names and modify
for your ranges to get it to work.
What did you try? Post your formula.
What happened ?

If all else fails, send to my PERSONAL email below a SMALL workbook and I
will look. Be sure to copy this email so I will know
Click to expand...
what
I am getting and
what to do with it. Reading minds is not one of my talents. I do admit it
would come in handy around here. <BFG>

--
Don Guillett
SalesAid Software
(e-mail address removed)
Tried this I am unable to get this to work

-----Original Message-----
try this idea
=INDEX(c1:c21,MATCH(MAX(g1:g21),g1:g21,0))

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hi

I have a problem with my presentation sheet for our
marching band championships.

I am referencing the Summary Sheet by category by band
to
place in the presentation sheet band placings
Click to expand...
(1st)
and
want to formula to populate the presentation sheet with
the bands name that has won 1st place with the trophy to
be awarded in the next column. Unfortunately I
Click to expand...
have
9
bands competing

This is my formula
=IF('S1'!$F$23=1,DATA!$C2,IF('S1'!$G$23=1,DATA! $C3,IF
('S1'!
$H$23=1,DATA!$C4,IF('S1'!$I$23=1,DATA!$C5,IF('S1'!
$J$23=1,DATA!$C6,IF('S1'!$K$23=1,DATA!$C7,IF('S1'!
$L$23=1,DATA!$C8,IF('S1'!$M$23=1,DATA!$C9,IF('S1'!
$N$23=1,DATA!$C10,IF('S1'!$O$23=1,DATA! $C11))))))))))

Excel limit on 7 if statement how can I change
Click to expand...
this
to
include 10.

Kind regards
Roe



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top