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

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

"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

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

On Apr 17, 1:10 pm, William Horton
wrote:

Bill,

Thank you for your reply.

<snip

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

Bill Horton

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

On Apr 17, 1:30 pm, Nikki wrote:
On Apr 17, 1:10 pm, William Horton

wrote:

Bill,

Thank you for your reply.

<snip

Hope this helps.

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

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

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

"Nikki" wrote:

On Apr 17, 1:30 pm, Nikki wrote:
On Apr 17, 1:10 pm, William Horton

wrote:

Bill,

Thank you for your reply.

<snip

Hope this helps.

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