Return value based on checkboxes selected -- IF() LOOKUP()

[Nikki]

Hi Group,

I know this can probably be done in Excel many different ways, but I
cannot seem to grasp exactly what needs to be in the formula.

I have 8 checkboxes that link to 8 different cells in a column
(U5:U12). Each checkbox refers to a certain amount of line pairs seen
(4, 5, 6, 7, 8, 9, 10, and/or 12 lp/cm). I would like to return the
highest number of line pairs seen. However, if, let's say, 8 lp/cm
(U9) and 10 lp/cm (U11) can be visualized, but not 9 lp/cm (U10), I
would only like to return "8". (I know this sounds odd)

To further explain: I'd like to return a value corresponding to the
most consecutive TRUE() statements, starting from the top (U5).

Column T is text; Column U is linked from the checkboxes

T U
5 4 lp/cm TRUE
6 5 lp/cm TRUE
7 6 lp/cm FALSE
8 7 lp/cm TRUE
9 8 lp/cm FALSE
----etc-----

Result returned: "5"

In another sheet I used a nested IF() function, but since there are
more than 7, this doesn't work. I'm sure a LOOKUP() function can be
used, but I just can't seem to put it together correctly.

I'd like not to use a two-step nested function if it's avoidable.

Thank you in advance,
Nikki

 

[Guest]

Perhaps you can use a helper column.
In cell V5 put the formula =IF(U5=FALSE,0,1)
In cell V6 put the formula =IF(U6=FALSE,0,V5+1)
Copy the formula in cell V6 to cells V7:V12
In whatever cell you want your answer to be put the formula
=OFFSET(T4,MATCH(MAX(V5:V12),V5:V12,0),0,1,1)

This seems to do it for me. If 8 lp/cm and 10 lp/cm are both TRUE it picks
8 lp/cm. If 7 lp/cm and 8 lp/cm and 10 lp/cm are all TRUE it again picks 8
lp/cm. Basically the most consecutive # of TRUES. If there is a tie it
picks the lower value.

Hope this helps.

Bill Horton

 

[dq]

Hi Group,

I know this can probably be done in Excel many different ways, but I
cannot seem to grasp exactly what needs to be in the formula.

I have 8 checkboxes that link to 8 different cells in a column
(U5:U12). Each checkbox refers to a certain amount of line pairs seen
(4, 5, 6, 7, 8, 9, 10, and/or 12 lp/cm). I would like to return the
highest number of line pairs seen. However, if, let's say, 8 lp/cm
(U9) and 10 lp/cm (U11) can be visualized, but not 9 lp/cm (U10), I
would only like to return "8". (I know this sounds odd)

To further explain: I'd like to return a value corresponding to the
most consecutive TRUE() statements, starting from the top (U5).

Column T is text; Column U is linked from the checkboxes

T U
5 4 lp/cm TRUE
6 5 lp/cm TRUE
7 6 lp/cm FALSE
8 7 lp/cm TRUE
9 8 lp/cm FALSE
----etc-----

Result returned: "5"

In another sheet I used a nested IF() function, but since there are
more than 7, this doesn't work. I'm sure a LOOKUP() function can be
used, but I just can't seem to put it together correctly.

I'd like not to use a two-step nested function if it's avoidable.

Thank you in advance,
Nikki

Hallo Nikki,

If the table you show is in S5:U9 this will do the trick:
=OFFSET($S$4,MATCH(FALSE,$U$5:$U$9,0),0)
If there is a possibility that all values are true, enter FALSE in U10
and and add an appropriate label in S10.

DQ

 

[dq]

Hallo Nikki,

If the table you show is in S5:U9 this will do the trick:
=OFFSET($S$4,MATCH(FALSE,$U$5:$U$9,0),0)
If there is a possibility that all values are true, enter FALSE in U10
and and add an appropriate label in S10.

DQ- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Sorry,

I'm wrong, the correct formula should be
=OFFSET($S$4,MATCH(FALSE,$U$5:$U$9,0)-1,0)
otherwise you return the first value that isn't visible.

DQ

 

[Nikki]

On Apr 17, 1:10 pm, William Horton

Bill,

Thank you for your reply.

Hope this helps.

Yes, this works great.






I did alter it a bit to return a string "N/A" if no checkboxes are
selected, by a simple IF() function.

Thank you again,
Nikki

 

[Nikki]

On Apr 17, 1:10 pm, William Horton


Bill,

Thank you for your reply.



Yes, this works great.

Rather, perhaps not. Sorry for the misleading congratulations before.
This formula does work correctly the way you are describing, but I
would like it to do something a bit different. Perhaps I wasn't clear
enough.

I'd like to return the highest lp/cm consistently visualized. In other
words, visualizations starting with 4 lp/cm. If 4 lp/cm isn't
visualized, it should return nothing - "n/a". If 4, 5, and 7 are
visualized, it should return "5". If 5, 6 and 7 are visualized, it
should still return "n/a" because 4 isn't visualized. If 4, 5, 6, 7
are visualized, it should return "7". Make sense?

Thanks again,
Nikki

 

[Nikki]

Sorry,

I'm wrong, the correct formula should be
=OFFSET($S$4,MATCH(FALSE,$U$5:$U$9,0)-1,0)
otherwise you return the first value that isn't visible.

DQ- Hide quoted text -

- Show quoted text -

DQ,

Thanks for your help. This formula does what I wanted it to...

Thanks again,
Nikki

 

[Guest]

Oh, I'm sorry. I misunderstood the specific requirements. In the case you
describe dq's second suggestion works best. You no longer need the helper
column either.

In the cell where you want the answer to be enter the following formula:
=OFFSET($T$4,MATCH(FALSE,$U$5:$U$12,0)-1,0,1,1)

In cell T4 enter whatever value you want to see if all values are FALSE
("#n/a"). Or add an additional row where the value is always TRUE and the
value in column T of that row is what you want to see if all other values are
FALSE.

This should be what you need.

Bill Horton