Lookup Question

hi - whats the condition that you are basing your coloring on?

sulakvea said:

hi - whats the condition that you are basing your coloring on?

Click to expand...

Hi,

I have times populated for fixtures from c10:c4

10:00
10:10
10:20 etc..

If say there are less teams, there will be less games therefore the timings will cut off

I have this formula to conditionally change the cell colour to white

=ROW(A1)>SUMPRODUCT(--($D$10:$D$24<>"")*--($D$10:$D$24>0))

This works ok but i need the lookup range to change based on the visible data

Hope this makes sense

I have come up with this formula

Will this work or is this a bd way to do it

=LOOKUP(9.9999999999E+307,INDIRECT("F7:F"&J5))

=index($c10:$c24,countif($d10:$d24,">0"))

mahmed1 said:

I have come up with this formula

Will this work or is this a bd way to do it

=LOOKUP(9.9999999999E+307,INDIRECT("F7:F"&J5))

Click to expand...

That'll probably work but I would use 1E100 instead of 9.9999999999E+307.

=LOOKUP(1E100,INDIRECT("F7:F"&J5))

I can't tell what range you need to look at. In your first post you refer to $C$10:$C$24 and in the second post you refer to $D$10:$D$24 and in the last post you refer to F7:F"&J5.

What's in J5? This formula:

=SUMPRODUCT(--($D$10:$D$24<>"")*--($D$10:$D$24>0))

Better written as:

=SUMPRODUCT(--($D$10:$D$24<>""),--($D$10:$D$24>0))

Maybe this is what you need:

=LOOKUP(1E100,F7:INDEX(F7:F100,J5))

Or, maybe even simply:

=INDEX(F7:F100,J5)

T. Valko said:

That'll probably work but I would use 1E100 instead of 9.9999999999E+307.

=LOOKUP(1E100,INDIRECT("F7:F"&J5))

I can't tell what range you need to look at. In your first post you refer to $C$10:$C$24 and in the second post you refer to $D$10:$D$24 and in the last post you refer to F7:F"&J5.

What's in J5? This formula:

=SUMPRODUCT(--($D$10:$D$24<>"")*--($D$10:$D$24>0))

Better written as:

=SUMPRODUCT(--($D$10:$D$24<>""),--($D$10:$D$24>0))

Maybe this is what you need:

=LOOKUP(1E100,F7:INDEX(F7:F100,J5))

Or, maybe even simply:

=INDEX(F7:F100,J5)

Click to expand...

Your formula works fab, i have simply used the indirect with lookup. I think its getting too late for me and im getting confused myself

why use 1E100 instead of 9.999999e+307

mahmed1 said:

Your formula works fab, i have simply used the indirect with lookup. I think its getting too late for me and im getting confused myself

why use 1E100 instead of 9.999999e+307

Click to expand...

Ok, I guarantee that you'll get the EXACT same result using either:

• 9.9999999999E+307
• 1E100

=LOOKUP(9.9999999999E+307,INDIRECT("F7:F"&J5))
=LOOKUP(1E100,INDIRECT("F7:F"&J5))

So, which one looks more compact and easier to remember?

Quick, without counting them, how many 9s are in 9.9999999999E+307 ?

Here's the secret to how that works...

The lookup_value has to be larger than any number in the range.

You know the data. If the the biggest number in the range will never be greater than 100 then the lookup_value simply needs to be greater than 100. Like 101...

=LOOKUP(101,INDIRECT("F7:F"&J5))

Since I don't know what kind of data you're dealing with I suggested using a gigantic number like 1E100 (scientific notation for the number 1 followed by 100 zeros).

There's a 100% chance that 1E100 is greater than any number you're dealing with and it's easier to remember and much shorter than 9.9999999999E+307.

mahmed1 said:

...

why use 1E100 instead of 9.999999e+307

Click to expand...

The latter, that is, 9.99999999999999E+307, is a constant standing for a limit in Excel, not a personal whimsey. What follows is a quote from Excel's Help, searchable under 'limits'...

http://office.microsoft.com/client/&app=ZXL&ver=14Calculation specifications and limits

<TABLE class=collapse><TBODY><TR class=trbgeven><TH>Feature</TH><TH>Maximum limit</TH></TR><TR class=trbgodd><TD>Number precision</TD><TD>15 digits</TD></TR><TR class=trbgeven><TD>Smallest allowed negative number</TD><TD>-2.2251E-308</TD></TR><TR class=trbgodd><TD>Smallest allowed positive number</TD><TD>2.2251E-308</TD></TR><TR class=trbgeven><TD>Largest allowed positive number</TD><TD>9.99999999999999E+307</TD></TR><TR class=trbgodd><TD>Largest allowed negative number</TD><TD>-9.99999999999999E+307</TD></TR></TBODY></TABLE>

Click to expand...

The above is consultable at all times.

One would hope that MS would give such constants names like BigNum (or MaxNum). This would help to eliminate zillions of variations.

Here some links on the BigNum and how/why it's used in look up functions
that are driven by a form of binary search:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)

An additional note

[1]

=LOOKUP(9.99999999999999E+307,A2:F2)

is the same thing as...

[2]

=LOOKUP(MAX(A2:F2)+Delta,A2:F2)

where Delta is a tiny number, say, 1.

Why the first formula then? Because it is faster (no extra function) than the second formula and the second formula won't survive if A2:F2 houses any error value which MAX can't cope with.

Any difficulty remembering 15 9's...

Consult Help for "limits";
Define a name like BigNum as referring to it;
Use a truncated version like 9.99E+307 in analogy to 3.14 which is known as Pi.

Aladin Akyurek said:

Any difficulty remembering 15 9's...

Consult Help for "limits";
Define a name like BigNum as referring to it;
Use a truncated version like 9.99E+307 in analogy to 3.14 which is known as Pi.

Click to expand...

Why go to the trouble of an extra unnecessary step of creating a defined name when simply using 1E100 will work just as well?

Can you show us a "real world" example where using 1E100 does not do the same thing as using 9.99999999999999E+307 ?