Problem with the right function Choosing dimension

**Simonszz** · 03-26-2018, 05:14 AM

Hi, I dont undersand how I am going to do this. Which function shall I use and so forth.

I want the cell to check for the right dimension so that V(m/s) dont go above 0,2 m/s. And if possible choose the dimension closest to 0,2<.

I've started with IF(E7;0,2<

what is missing? or how do I need to think?

Anyone got a clue?

cheers

//Simon

**PaulM100** · 03-26-2018, 05:34 AM

So, to find the closest value that does not go over 0.2 for V(m/s) use the following formula:

=MAX(IF($S$7:$S$17<0.2,$S$7:$S$17)) - Array entered: Press Ctrl+Shift+Enter simultaneously instead of regular Enter.

Now, to find the m2, your logic is not clear. if you want the smallest value in the range use =MIN($R$7:$R$12)
To find the one than matches the closest V(m/s) value to 0.2 use =INDEX($R$7:$R$13,MATCH($A$13,$S$7:$S$13,0))

if there is other logic, please explain

**Olly** · 03-26-2018, 05:40 AM

Try:

Formula:

Please Login or Register  to view this content.

Although I suspect your version of Excel doesn't support MAXIFS - so try this:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**Simonszz** · 03-26-2018, 08:11 AM

Okey, the thing is this, in order to find out the speed V (m/s) of the water I have to have "flöde" (volume) / m2 (area).

So what im looking for is =C7*0,36/R8<0,2 and then for it to check if that answear is bellow 0,2 m/s and if that m2 (D7) is closest to that value. ( So I dont get to big pipes, becasue it costs more).

I want a kinda automtic solution that looks at the speed and says you need this size in cell D7.

maby im confusing you more than before...

**Simonszz** · 03-26-2018, 08:20 AM

.... so basiclt i want E7 to find value bellow 0,2 and D7 to tell me which dimension that is.

**PaulM100** · 03-26-2018, 08:41 AM

Use this for E7:

=MAX(IF($S$7:$S$17<0.2,$S$7:$S$17)) - Array entered: Press Ctrl+Shift+Enter simultaneously instead of regular Enter.

And for D7 the value should be 15 if in E7 you have 0.16099?
IF not, what should be the desired result?

**MrShorty** · 03-26-2018, 08:52 AM

This looks to me like a (binary) lookup kind of problem. It is not clear to me exactly what you want. If you are unfamiliar with Excel's lookup functions, I might suggest that you start with https://support.office.com/en-us/art...9-533f4a37673a Note the behavior described by the 3rd argument of the MATCH() function. Unfortunately, it seems that almost all lookup examples on the internet are for "exact match" lookups (3rd argument =0) and none illustrate the binary search behavior.

With your data, V is sorted in descending order, so we could first choose -1 for the 3rd argument =MATCH(0.2,$S$7:$S$47,-1) would return 1, because 0.2 is between 0.24 and 0.16. When V is sorted in descending order and lookup type is -1, MATCH() will return the row number for the value just larger than lookup value. On the other hand, if V is sorted in ascending order and lookup type is 1, then MATCH(0.2,$S$7:$S$47,1) will return the row number corresponding to the 0.16 value. Choose the scenario that best matches what you are trying to do.

Once you have the row number from the MATCH() function, then INDEX() functions can be used to return values from the other columns.