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

On 17 apr, 18:39, Nikki wrote:
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

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

On 17 apr, 20:18, dq wrote:
On 17 apr, 18:39, Nikki wrote:





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- 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

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

On Apr 17, 1:21 pm, dq wrote:
On 17 apr, 20:18, dq wrote:





On 17 apr, 18:39, Nikki wrote:

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- 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- Hide quoted text -

- Show quoted text -

DQ,

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

Thanks again,
Nikki