Can I use a formula for introducing a formula?

[MFT]

Hi!

I've got a calendar with agricultural activities. The first column are dates and the rest of columns represent plots, with codes of agricultural activities carried out on certain dates on each plot. My objective is to make a new calendar, filling in all the cells with a value.

This value is defined by a different formula, depending on the last activity carried out in the plot (depending on the last code). I don't know how to introduce the correct formula depending on the code, and I don't either know how to expand that formula to all the cells below, until the next activity (next code).

Any idea?

Attached is a simplified version of the table.

Thanks in advance!

[teylyn]

Hello MFT,

Is there a reason your dates are all in 1994? Not that it matters, but it would give some strange results, don't you think?

The formula can be constructed quite easily, but I don't know what to do with the 0.9 values in the Activities colum. There is no Activity of that type in your formula reference.

Can you explain?

[MFT]

Hi Teylyn, thanks for your answer.

The document attached is not the original, is just an example I did for ilustrating the problem. I put dates of 1994 because my data starts the 1st of January of 1994 and finishes in 2009, so is a quite big table. The problem is more complicated, but this is the point were I'm stuck now, and the file I posted is an example of it.

However, I don't understand why you say that working with the year 1994 the results will be strange. Does it matter?. I expect in all my cells a value between 0 and 1, and I don't think the year has any influence on that.

The table starts with 0,9 because I don't know the activity carried out before the 01.01.1994 and I must make an assumption for the value.

I hope that clarifies your questions.

Thanks!

[teylyn]

Well, since you don't specify what to do with cells that have 0.9 as a value, I start my formula in J6

=IF(ISBLANK(F6),J5,IF(F6="Sowing",J5-0.1*((TODAY()-E6)/365),IF(F6="Harvest",0.2+0.8*((TODAY()-E6)/365),IF(F6="Plough",1-0.2*((TODAY()-E6)/365),J5))))

Depending on your country settings, you may need to use this version instead, using semicolons as delimiters and decimal comma instead of decimal point:

=IF(ISBLANK(F6);J5;IF(F6="Sowing";J5-0,1*((TODAY()-E6)/365);IF(F6="Harvest";0,2+0,8*((TODAY()-E6)/365);IF(F6="Plough";1-0,2*((TODAY()-E6)/365);J5))))

The formula looks at column F in the same row. If this is blank, the value from one cell above is returned. If column F has "Sowing", then the calculation specified will be performed, if it is "Harvest", the calc you specified for harvest will be performed, etc. and if none of the specified terms are found, then the value from the cell above will be returned.

I expect in all my cells a value between 0 and 1,

This does not manifest. So there's either something wrong with the formulas you specified or the way I interpreted your spec. You say in your spreadsheet:

"i is present day"
"x is day of activity"

I used TODAY() for present day, and that is why I was wondering about 1994 dates.

Attached is the file with the above formula installed from J6 onwards.

I have no clue what this is all about, so if it does not meet your needs, you'll have some more explaining to do. Don't assume that we know your subject matter. You will need to explain the logic. It would be good to see a few mocked up expected results.

cheers

[MFT]

Hei

I'm afraid that the problem is more complex. I think you are right, I didn't explain myself correctly. I enclose a new copy of the file with results calculated applying the formulas manually. I also added a new activity. I hope this brings more light over the issue.

I have no clue what this is all about, so if it does not meet your needs, you'll have some more explaining to do. Don't assume that we know your subject matter. You will need to explain the logic.

First I'll try to explain what this is about. I'm working with a mathematical model that computes risk of soil erosion. One of the components of the model is a factor "C" that varies daily, and that is what I'm trying to obtain. This value C depends on the tillage and management practices carried out in agricultural plots. C is the rate of risk of erosion (that is why it must vary from 0 to 1. If is 0, the risk is 0 and if it is 1, the risk is maximum.

For example, one can plough the land and destroy the structure of the soil. Then, you can harrow, making the soil dust. Those activities increment the erosion and rise the value C. For harrow, for example, we retake the previous value of plough and we add a rate of change:

Previous - 0,3 * ( (i-x) / 365 )

It means that the C value will follow its previous evolution (due to plough) but after 365 days C will be reduced 0,3. "i" is any day after harrow and "x" is the day of harrow.
The day of harrow -0,3 * ( (i-x) / 365 ) = 0
The day 365 after harrow -0,3 * ( (i-x) / 365 ) = 0,3
This means that 365 days after harrow the soil is 30% less erodible than it was the day of harrow.


I don't know if I explained myself correctly, but I tryed.

Well, since you don't specify what to do with cells that have 0.9 as a value, I start my formula in J6

The value 0,9 was introduced by me arbitrarily, as starting point of the calculation, because I don't know the activity carried out before the 1st of January of 1994 and I need a previos value for calculate C for the first activity (sowing). You are right, the calculation should start with sowing and don't do any calculation with the cells occupied by 0,9.

used TODAY() for present day, and that is why I was wondering about 1994 dates.

I'm sorry for the missunderstanding, but what I call "i" is not TODAY, it is the correspondent date of the cell (the head of the row). This is the reason why your values are much bigger than 1. "i" is the factor that actualizes C to the current day.

=IF(ISBLANK(F6);J5;IF(F6="Sowing";J5-0,1*((TODAY()-E6)/365);IF(F6="Harvest";0,2+0,8*((TODAY()-E6)/365);IF(F6="Plough";1-0,2*((TODAY()-E6)/365);J5))))

The formula looks at column F in the same row. If this is blank, the value from one cell above is returned. If column F has "Sowing", then the calculation specified will be performed, if it is "Harvest", the calc you specified for harvest will be performed, etc. and if none of the specified terms are found, then the value from the cell above will be returned.

If the cell corresponds with one activity, it should apply the formula of the activity (when i=x), that is right. But, if the cell is blank, it shouldn't copy the value of the cell above, it should copy the formula of the last activity carried out, actualizing that formula for the date of the blank cell (i).

It is important to notice that some activities inherit the formula of the previous activity and add a new formula. In some cases they don't inherit a formula, but a constant value.

Respect to your solution for introducing the formulas, I can't do it that way since I have 140 activities and I can't introduce all them together with IF formulas as you did. My table has 50 columns and 6000 rows. The computer can get mad.

I think that this is all. If anything is still unclear I will try to explain it again.

Thanks a lot for your patience