return value dependent on value in other cell

Hi experts,

I have a table of irregular entries which are related to dates. In the first row are the dates when sampling happened, in all following rows are continous growth data entered but sampling took not place at all places at the same dates. Now I want to get the difference between the first and last value in one row and divide it by the number of days between both samplings to get the average daily growth rate. (Hope that is understandable)

Now I got the first value through: {=INDEX(E2:AH2,MATCH(1,--(E2:AH2 >0),0))}
and the last value through: =INDEX(E2:AH2,MATCH(9.99999999999999E+307,E2:AH2))

What I need now is a formula that returns me the value of row 1 for the coloum with the first and the last value was found, and subtract them from each other.

Any idea?
Sorry if I answer a bit slow, but I will get back for sure.

Thanks for your creative solutions.
Best, Tassss

Nobody any hint?
Can you at least let me know if this is possible or if you need a macro to do that?

Cheers,
Tassss

Originally Posted by ChemistB

Perhaps you can upload an example spreadsheet showing how your data is laid out with an example of what you are looking for?

=(INDEX(E$2:AH$2,MATCH(9.99999E+307,E2:AH2))- INDEX(E$2:AH$2, MATCH(TRUE,INDEX($E$1:$AH$1>0,0),0)))/(INDEX(E$1:AH$1,MATCH(9.99999E+307,E2:AH2))- INDEX(E$1:AH$1, MATCH(TRUE,INDEX($E$1:$AH$1>0,0),0)))

(INDEX(E$2:AH$2,MATCH(9.99999E+307,E2:AH2)) should return the first value in row 2 (assuming that's where your data begins)
INDEX(E$2:AH$2, MATCH(TRUE,INDEX($E$1:$AH$1>0,0),0)) returns the last value in Row 2
(INDEX(E$1:AH$1,MATCH(9.99999E+307,E2:AH2))- INDEX(E$1:AH$1, MATCH(TRUE,INDEX($E$1:$AH$1>0,0),0))) subtracts last date from first date

Dear ChemistB,
thanks for your answer. I see that I don't make myself understood... hm, I will try it this way.
What was done
Several independent teams went out and sampled animals at different sites and at different days. This sampling was repeated once a month but not always exactly after 30 days.
The data set
As you can see in the posted snapshot above, row1 contains all the dates of all samplings. Let's assume that there are 3 or 4 dates in every month in which samples where taken. In all following rows you have sample data: for every animal we sampled, one row. But as these animals are in different locations, they were not sampled all at the same days. Some were sampled the first sample day in a month, some the 2nd, 3rd or 4th. The date on which animals got sampled is a bit random.
What I need
So now I have all the sampling dates of ALL samplings over a period of 6 months in row 1 but in the following rows the values are only put under the day the sampling took place (obviously). In consequence, there are many empty cells in each row more or less randomly distributed. What I need is a formula which returns the value in row1 that correlates to any specific value I have in all following rows. In other words: If I identify the last value in row 2, I want the formula to return the value of the same column in row one. I want to get the date when this sample was taken from row 1.

If I get this, then I can use this value to calculate the following:

[value rowX:col*]-[value rowX:col$]/([date row1:col*]-[date row1:col$])

where * represents the last value in the sample row and $ the first value in the sample row (sample row as contrast to the first row where I have the dates).

Is that clearer?

Thanks so much.
Cheers,
Tassss.

Originally Posted by ChemistB

I don't see a posted snapshot. I posted example spreadsheet works better because then we can try our formulas in this example spreadsheet and send it back to you (Go Advanced>Manage Attachments).
Will you always be looking for the last value - first value? or might it be next to last value - first value?

Ok, I uploaded an example file. Well, the first and the last is the most important to analyse the change over the whole period. If the selection of specific values is possible (e.g. 2nd, 3rd, 4th value in a row) that would be fantastic. But if that is too complicated I could live without it, I guess.

Thanks so much, your help is really great.
Best,
Tass.

PS: The capture above is posted inline, maybe for that reason you did not see it (excel capture.JPG)

Originally Posted by ChemistB

Here's the solution using first and last values/dates In AK2 dragged down (I didn't use your values in AI and AJ so you can delete them if you need to)

=(INDEX($E2:$AH2,MATCH(9.99999E+307,$E2:$AH2))- INDEX($E2:$AH2, MATCH(TRUE,INDEX($E2:$AH2>0,0),0)))/(INDEX($E$1:$AH$1,MATCH(9.99999E+307,$E2:$AH2))- INDEX($E$1:$AH$1, MATCH(TRUE,INDEX($E2:$AH2>0,0),0)))

If there's only 1 value or if the first and last date are the same, then you'll get a #Div/0 Error. You can wrap your formula in IFERROR or do an IF statement to remove that error.

I will work on the inbetween values and see if I can come up with something.

Fantastic! I am impressed. Thanks so much,great to have guys like you around who help so selflessly.
That makes my live so much easier. I am really happy about that.

Best wishes,
Tassss.

Originally Posted by ChemistB

Hi Tassss,
Here's the formula for (each value - First)/# of days. It's an array (CNTRL SHFT ENTER)
In AH2 dragged across and down

=IF(COUNT($E2:$AH2)>COLUMN(A1), (INDEX($E2:$AH2,LARGE(IF(ISNUMBER($E2:$AH2), COLUMN($E2:$AH2)-COLUMN($E2)+1,0),COLUMN(A1)))- INDEX($E2:$AH2, MATCH(TRUE,INDEX($E2:$AH2>0,0),0))) /(INDEX($E$1:$AH$1,LARGE(IF(ISNUMBER($E2:$AH2),COLUMN($E2:$AH2)- COLUMN($E2)+1,0),COLUMN(A1)))- INDEX($E$1:$AH$1, MATCH(TRUE,INDEX($E2:$AH2>0,0), 0))),"")

The IF(COUNT($E2:$AH2)>COLUMN(A1), part removes those pesky errors if the # of values in that row is less than or equal to the position you are looking for (1st latest, 2nd latest, etc)

See attachment

Hey ChemistB,

that looks great. But I don't understand the formula and also not how to use it. What do I have to change at the formula to determine which value will be chosen to be subtracted from the first one?
Thank you so much for your valuable input.

Best,
Tassss.

Originally Posted by ChemistB

COLUMN(A1) is the key term. COLUMN(A1) resolves to 1 which tells excel, the last sample
As you drag it to the right (as in my spreadsheet) as COLUMN(B1) =2 is second from last, 3 is 3rd from last.

Formula:

Please Login or Register  to view this content.

The array
IF(ISNUMBER($E2:$AH2), COLUMN($E2:$AH2)-COLUMN($E2)+1,0) returns a relative row number that all the numbers are in, with zeros filling in the values where there is no number
So in my sheet, for row 2
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,0, 0, 0, 0, 23, 0, 0, 26, 27, 0, 0,0

Then LARGE(that array, COLUMN(B1)) returns 26

INDEX(E2:AH2, 26) returns 9
Voila!

Voila! :D Not so voila for me, I would never have been able to develop that myself. Thanks again, excellent job. And well earned stars..

Cheers,
Tassss.