If Formula

[Redi]

Hi all,

here is the layout of my spreadsheet:

a3=apple
a4=orange
a5=pear

b2=red
c2=green
d2=yellow
e2=blue

b3=1
c4=1
d5=1
e5=1

f3=formula
f4=formula
f5=formula

g3=formula
g4=formula
f4=formula

column f formula: if there is a 1 on b3:e3, give me the color text
column g formula: if there is another 1 on b3:e3, give me the next
color text

thank you in advance!

 

[Domenic]

Is this what you mean?

F3, copied down:

=IF(ISNUMBER(MATCH(1,B3:E3,0)),INDEX($B$2:$E$2,MATCH(1,B3:E3,0)),"")

G3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(COUNTIF(B3:E3,1)>1,INDEX($B$2:$E$2,SMALL(IF(B3:E3=1,COLUMN($B$2:$E$2)
-COLUMN($B$2)+1),2)),"")

 

[Lars-Åke Aspelin]

Hi all,

here is the layout of my spreadsheet:

a3=apple
a4=orange
a5=pear

b2=red
c2=green
d2=yellow
e2=blue

b3=1
c4=1
d5=1
e5=1

f3=formula
f4=formula
f5=formula

g3=formula
g4=formula
f4=formula

column f formula: if there is a 1 on b3:e3, give me the color text
column g formula: if there is another 1 on b3:e3, give me the next
color text

thank you in advance!

Try the following formula in cell F3:

=IF(SUM(B3:E3),INDEX(B$2:E$2,MATCH(1,B3:E3,0)),"")

and the following formula in cell G3:

=IF(SUM(B3:E3)>1,INDEX(B$2:E$2,SMALL(IF(B3:E3=1,COLUMN(B:E)-1),2)),"")

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Copy cells F3:G3 down thru F5:G5

Hope this helps / Lars-Åke

 

[Luke M]

In F3:
=IF(COUNTIF(B3:E3,1)=0,"",INDEX($B$2:$E$2,1,MATCH(1,B3:E3,0)))
In G3
=IF(COUNTIF(B3:E3,1)>0,IF(LOOKUP(1,B3:E3,$B$2:$E$2)<>F3,LOOKUP(1,B3:E3,$B$2:$E$2),""),"")

Copy down as needed. (Note that MATCH is looking for first instance of "1",
while LOOKUP" is taking last instance. Thus, this can not be simply expanded
to column H to find a third "1")

 

[Redi]

Thanks guys. they both worked fine. I chose to go with the second one.
thank you for all the time you saved me!

 

[Redi]

Hey Luke M,
can you help me with a formulate that can run into the consecutive
columns H, I, J, K, L and so forth, to find a third, fourth, fifth,
sixth, and so forth... 1.
Thanks in advance.

 

[Lars-Åke Aspelin]

Hey Luke M,
can you help me with a formulate that can run into the consecutive
columns H, I, J, K, L and so forth, to find a third, fourth, fifth,
sixth, and so forth... 1.
Thanks in advance.

Well, in order to have more than 4 ones you have to move your result
to the right a bit.

I have generalized the formula you got previously.

First you should name the range with you colors as colors.
(in your small example the range is B2:E2)
Name the first cell of your result as result
(in your small example this range is F3)

The put the following formula in the first cell of your result.

=IF(SUM(OFFSET(colors,SUM(ROW()-ROW(colors)),0))>COLUMN()-COLUMN(result),
INDEX(colors,SMALL(IF(OFFSET(colors,SUM(ROW()-ROW(colors)),0)=1,
COLUMN(colors)-1),1+COLUMN()-COLUMN(result))),"")

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Copy to the right as far as the most possible number of ones (colors)
Copy down as far as you have data (matrix of ones under your colors).

Hope this helps / Lars-Åke

 

[Redi]

Lars-Åke Aspelin, thanks!
your formula works great with my little example. However, when i try
it with my actual worksheet, the results are not coming out correct.
It is giving me the eight color down from the actual/right color. This
is the case with all results. I am not sure whats causing this. Do you
have any idea?

 

[Lars-Åke Aspelin]

Lars-Åke Aspelin, thanks!
your formula works great with my little example. However, when i try
it with my actual worksheet, the results are not coming out correct.
It is giving me the eight color down from the actual/right color. This
is the case with all results. I am not sure whats causing this. Do you
have any idea?

I don't understand what you mean by "the eight color down".
Please give some example from your actual worksheet.
What is the result you get from the formula?
What is the result you expect?

Lars-Åke

 

[Redi]

I think that i solved what i was looking to do.

Changed the formula from -1 to -9.


=IF(SUM(OFFSET(colors,SUM(ROW()-ROW(colors)),0))>COLUMN()-COLUMN
(result),
INDEX(colors,SMALL(IF(OFFSET(colors,SUM(ROW()-ROW(colors)),0)=1,
COLUMN(colors)-9),1+COLUMN()-COLUMN(result))),"")

Thank you!!!

 

[Lars-Åke Aspelin]

I think that i solved what i was looking to do.

Changed the formula from -1 to -9.


=IF(SUM(OFFSET(colors,SUM(ROW()-ROW(colors)),0))>COLUMN()-COLUMN
(result),
INDEX(colors,SMALL(IF(OFFSET(colors,SUM(ROW()-ROW(colors)),0)=1,
COLUMN(colors)-9),1+COLUMN()-COLUMN(result))),"")

Thank you!!!

OK, now I see. If your colors are not starting in column B you will
have the wrong result if you just subtract 1.
The thing you should subtract is MIN(COLUMN(colors)-1
giving the following formula that I hope should survive insertion of
new columns to the left for example.

=IF(SUM(OFFSET(colors,SUM(ROW()-ROW(colors)),0))>COLUMN()-
COLUMN(result),INDEX(colors,SMALL(IF(OFFSET(colors,SUM(ROW()-
ROW(colors)),0),COLUMN(colors)-MIN(COLUMN(colors))+1),
1+COLUMN()-COLUMN(result))),"")

Hope this helps / Lars-Åke