Hydraulic pump test results - report

[taylorcharley]

Hello

We have a test rig which gives us readings of flow and pressure every second for each section of the pump. Our engineer has created a spreadsheet to record the results and a test report to give an overview of the results in a graph.

There are two tabs on the excel file. The first is labelled data and is populated direct from the test rig. The second tab is the results page.

I am attaching a version of the report (whp test report). There should be results for pressure from 50 - 200 but there are missing values (flow) on one section for 200 and the other section 175 & 200. With different results there are different missing values and we are wondering if there is a better formula to use to arrive at the same result?

The formula we are using is:

=INDEX(DATA!O2:O52000,MATCH(B13,DATA!F2:F52000)+(LOOKUP(B13,DATA!F2:F52000)<>B13))

Can you suggest a solution? Thank you. Charlie

[tim201110]

You use match type 1 (omitted), and MATCH finds the largest value that is less than or equal to lookup_value. In this case the values in the lookup_array argument must be placed in ascending order. They are not.
for column E drag and pull down
=MAX(IF(DATA!$I$9:$I$55000=LARGE(IF(DATA!$I$9:$I$55000<=D13,DATA!$I$9:$I$55000),1),DATA!$L$9:$L$55000)) as array formula

[taylorcharley]

Hi Tim,

thank you for your reply.

with the formula you suggested does the data have to be placed in ascending order as well? I have tried playing about with it but it seems to return 0 for every value

[tim201110]

you don't need to place data in ascending order using this formula

[Glenn Kennedy]

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[taylorcharley]

Thank you so much! We've got it to work! Have a good day.

[taylorcharley]

Thank you! That did the trick and we've got it to work!