Re: If Formula
Domenic
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)),"")
--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
In article
<d0a60659-e5dd-4e5e...@v2g2000vbb.googlegroups.com>,
Redi <redisk...@gmail.com> wrote:
> 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!
Lars-�ke Aspelin
<redisk...@gmail.com> wrote:
>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
=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")
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
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
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).
Lars-�ke Aspelin
<redisk...@gmail.com> wrote:
>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
Lars-�ke Aspelin
<redisk...@gmail.com> wrote:
>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))),"")