Excel 2007 - Formula problems 2

[woodduck09]

I am writing a formula for fire engineering, the formula spreadsheet requires an iterated solution. I have time stepped my spreadsheet every 1 second. To find my answer i have written the code as follows:-

=MATCH($B$7,L2:L3601)
where:- $B$7 is my variable and the L column is where the worked solution is. The 'match' is the time in seconds.

The problem is - when I change my time step to anything other than 1 second, the 'match' doesn't evaluate the correct answer.

The formula obtains the information from the same table, the match will not match correctly.

thanks in advance

 

[SkipVought]

Hi,

The problem is - when I change my time step to anything other than 1 second, the 'match' doesn't evaluate the correct answer.

Please explain what that means.

MATCH returns a row offset relative to where the lookup is located in the lookup range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[woodduck09]

Hello Skip,

The 'match' is the time in seconds. In the 1 second intervals it returns the correct value.

When I change the table to a 5 second interval (and .5 second) it picks up a value that is nowhere near what is calculated.

eg.
1 second interval - match = 198 seconds (correct answer)
5 second interval - match = 62 seconds
.5 second interval - match = 344 seconds

However, the formula doesn't change, the value doesn't change - just the answer, but only when the time step is changed

 

[SkipVought]

Please! you have not explained HOW your process is working!

Please post a small sample of your lookup data and a corresopnding lookup value. Then explain what works and what does not work USING the sample data you posted -- The ENTIRE process, please!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

1.I am wondering if Goal Seek will help you achieve your (less than clear) overall objective.

The Goal Seek feature in Excel 2007 is a what-if analysis tool that enables you to find the input values needed to achieve a goal or objective. To use Goal Seek, you select the cell containing the formula that will return the result you’re seeking and then indicate the target value you want the formula to return and the location of the input value that Excel can change to reach the target.

2. I assume you realise that Match returns the relative position of the best match, not the value of that match???
With the default value for matchtype (you have not set this parameter):

MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order

Gavin

 

[woodduck09]

Sorry I haven't been clearer, just trying to work out how to attach the file - I appreciate your patience

Each page shows the same spreadsheet with the different time steps - I have highlighted the line that shows the different problems.

http://www.mediafire.com/file/drhih2mmzzm/Ass

5.xls - is where I have posted the file

I hope this is enough - Thanks

 

[Gavona]

As per my second suggestion you have not understood the Match function.
For Detector Activation Time try =INDEX(I2:M3601,MATCH($B$7,L2:L3601),1)
The last parameter identifies which column to take the value from, 2 would give gas temp.

Gavin

 

[woodduck09]

Gavona,

Thanks very much, I entered the info but it returned a value of "False" rather than the numerical value.

I have added a set of brackets to the "Match" statement and that has fixed the error.

=INDEX(I2:M3601,(MATCH($B$7,L2:L3601)),1)

Champion stuff

Greatly appreciated