Feasability of checking 2 cells to show picture (E2003 SP3)

[steveh]

Good morning

I have 4 cells that uses an index and match formula get their values from a large table that shows the monthly high and low temperatures for most world wide locations, so for example if somebody selected January shipping from Sofia, Bulgaria to Singapore it would show up like this

High,low.jpg

I then have 2 other cells with Min and Max formulas to show the lowest temperature extreme to the highest

packing.jpg

Because we ship temeprature sensitive pharmaceuticals we have dozens and dozens of different packaging solutions to meet different solutions so I was thinking that if I had a list of the packing on another sheet and their extremes of operation would it be possible based on the value of the 2 cells above to have an answer automatically pop up saying, for example "you require a cL9003 for this shipment"

[HansV]

I imagine that there can be multiple packaging solutions for a given temperature range. What would you like to display - the first one that meets the criteria, or ...?

[steveh]

Hi Hans

Yes, the first one would be fine because I would ask our in house packing guy who knows all of the box ranges to make it so that it will be relevant to what I am trying to do for him.

[HansV]

See if you can adapt the attached sample workbook to meet your needs.

Packaging.xls

[steveh]

Hi Hans

Thank you very much for this, your example works fine but I am having a little trouble applying it to a sample workbook that I have, which has, I think, every type of temperature combination from -25 through to 40. I will keep trying different things before I raise the white flag and call for help though.

Ta

[steveh]

OK I

I have tried to replicate Hans solution to a huge range of 'spurious' data (spurious because I do not know all of the temperature ranges at the moment) but I cannot get the result to show anything other than N/A.

Any pointers please

[GeoffW]

I have a preference towards a VBA solution in this sort of case- because it's much easier for me to debug than a long IF statement.

Are you OK with VBA?

[steveh]

I have a preference towards a VBA solution in this sort of case- because it's much easier for me to debug than a long IF statement.

Are you OK with VBA?

Hi Geoff

By the standards of some of my formulas I thought 1 If was good!! I will have a go with a VB solution and post back if (when ) I need help

[HansV]

I cannot get the result to show anything other than N/A.

The formula in G4 needs to be an array formula, i.e. you must confirm it with Ctrl+Shift+Enter.

Try this shorter version (again, confirm with Ctrl+Shift+Enter):

=MIN(IF(($B$2:$B$2161<=F2)*($C$2:$C$2161>=G2),$A$2:$A$2161,2000000))

[steveh]

Thank you Hans

That works great

[steveh]

See if you can adapt the attached sample workbook to meet your needs.

Packaging.xls

Hi Hans

In the above attachment you gave me the following formula

=IF(MAX(ROW($A$2:$A$15)*($B$2:$B$15<=F2)*($C$2:$C$15>=G2))=0,"N/A",INDEX($A$1:$A$15,MAX(ROW($A$2:$A$15)*($B$2:$B$15<=F2)*($C$2:$C$15>=G2))))

I am trying to set it up in a WS using different rows and columns

Package_Selection.jpg

and have changed the formula to this

=IF(MAX(ROW($R$9:$R$22)*($S$9:$S$22<=R23)*($T$9:$T$22>=S23))=0,"N/A",INDEX($R$8:$R$22,MAX(ROW($R$9:$R$22)*($S$9:$S$22<=R23)*($T$9:$T$22>=S23))))

I must have made a stupid mistake somewhere because I get an error value, I have printed out an compared both forulas but I can't see the wood for the tree's. Can you see my mistake?

[HansV]

It should be

=IF(MAX(ROW($R$9:$R$22)*($S$9:$S$22<=R23)*($T$9:$T$22>=S23))=0,"N/A",INDEX($R$1:$R$22,MAX(ROW($R$9:$R$22)*($S$9:$S$22<=R23)*($T$9:$T$22>=S23))))


And of course, make sure that you confirm the formula with Ctrl+Shift+Enter. It is an array formula.

[steveh]

Thank you Hans

Can you please explain for me R1, in your example you started in A1 with the heading and then values in A2, I assumed, and obviousely wrongly, that because my heading was in R8 and the values started in R9 this part of the formula was referring to the heading.

[HansV]

MAX(ROW($R$9:$R$22)*($S$9:$S$22<=R23)*($T$9:$T$22>=S23)) returns the highest row number in the range R9:R22 for which the cell in column S is less than or equal to R23 and the cell in column T is greater than or equal to S23. This will be a number in the range 9, 10, ..., 22.

We want to use this row number to specify a cell in column R. If we used INDEX(R8:R22, ...), the row number would be offset from R8, but we want to start counting the rows at R1.

[steveh]

Hi Hans

Thanks for that, I am a little clearer, but one more thing if I may. What if I had other data in R1 - R7 that was not associated with this formula would it ignore it or would I have to make sure that this column was used for nothing else?

[HansV]

It doesn't matter what the cells R1:R8 contain. The number returned by MAX(ROW($R$9:$R$22)*($S$9:$S$22<=R23)*($T$9:$T$22>=S23)) will either be 0 (if not found) or at least 9, so you'll never look up a value in R1:R8.