• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to Pick up value from another cell and delay by a year

rajkenya1

Member
Hi Guys

Need some help on the following.

I have in the attached spreadsheet in column C capital spend for 3 years. I need a formula in cell D4 which basically says if spend for the total 3 years is above 2.5m than choose value in cell c9 but delay it by one year so that the higher value appears in cell e4 and d4 still has the lower value. So a kind of a delay. Also within that formula even though the total for the 3 years is above 2.5m year 1 still has only 500k which is lower than the 2.5m hence the value in cell d4 should only be 34. BUt the formula needs to be flexible incase i change the amount.

I have tried to manually to insert it in the spreadsheet but hoping someone can help me come up with a formula

Cheers

Raj
 

Attachments

Hui

Excel Ninja
Staff member
I'm not sure but in D4:
=IF(AND($C$11>=2500000,D2="Year 1"),C9,VLOOKUP(C2,$B$4:$C$6,2))
Copy across
 

Hui

Excel Ninja
Staff member
Whoops my mistake
I forgot to tell you to Change B4:B6 to that as shown below
upload_2015-2-19_10-34-51.png

If this isn't correct, please post a sample of what the answer should be
 

rajkenya1

Member
Hu Hui

Thanks for your reply again.

I think i may have gotten you confused.

See attached marked in yellow what the answer should be. Basically in year one the total spend is 500k so the value in D4 should be 34, same in E4 as the total spend in year 1 and 2 is 1.5m. Now In F4 it still should be 34 even though the total of the 3 years is now 2.5m. This is because i want the higher value of 52 to be delayed by a year.


Here is the scenario. A construction project is going on for 3 years, as soon as the spend becomes 2.5m or more a higher rate can be charged but this can only be charged in the following year as assumed construction will take the current year in our case year 3 end to finish. so in year 4 we can start charging 52.

Hope this makes sense.
 

Attachments

Hui

Excel Ninja
Staff member
Rajkenya

Pls see attached file

But the issue is

If it is less than 2.5 Million then only Yr 1 & 2 should be 34
If Less than or equal then all years should be 34

If you put $1 into Year 4
then you get your answer
upload_2015-2-19_10-57-6.png
 

Attachments

rajkenya1

Member
Hi Hui

Thanks again for your email.

You are a clever man and nearly getting there.

Ok so basically year 1 and 2 are less than or equal to 2.5m so the value should be 34. Now in year 3 although the spend is now greater than or equal to it should the value should still be 34 and only in year 4 onwards it should be 52. What i am trying to do is delay the higher value by one year after the spend is greater than or equal to 2.5 m.

so its should be as follows

Year 1 = 34
Year 2 = 34
Year = 34 (even though the spend has hit 2.5m)
Year 4 = 52
Year = 52

However the formula should be flexible in that if spend 1.5m in year 1 and 1m in year 2 then it should be as following.

Year 1 = 34
Year 2 = 34
Year 3 = 52
Year 4 = 52
Year 5 = 52

I am really grateful for your help.

Kind regards
 

rajkenya1

Member
Hi Hui

Back to this formula issue. Thanking you for your help earlier.

I tried to extend the formula as per the attached spreadsheet but for some reason it works fine upto year 10 but after 11 it doesnt seem to pick up the correct value.

Are you able to assist?

Thanks in advance.
 

Attachments

Top