# 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

• 9 KB Views: 4

#### 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

#### rajkenya1

##### 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
Hi Hui

Thanks for your email and looking into it.

Copied into it but doesnt seem to work.

See attached file.

Thanks though my friend.

#### Attachments

• 9.5 KB Views: 3

#### Hui

##### Excel Ninja
Staff member
Whoops my mistake
I forgot to tell you to Change B4:B6 to that as shown below

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

#### rajkenya1

##### Member
Hu Hui

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

• 9.1 KB Views: 2

#### 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

#### Attachments

• 9.7 KB Views: 3

#### rajkenya1

##### Member
Hi Hui

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

#### Hui

##### Excel Ninja
Staff member
D4: =IF(SUM(OFFSET(\$C\$3,,,COLUMNS(\$C\$2:C2)))<2500000,\$C\$10,\$C\$11)
Copy across

#### rajkenya1

##### Member
Hui

YOU GENIUS.

Thank you Hui. Thank you.

Staff member

#### 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?

#### Attachments

• 10 KB Views: 3

#### Hui

##### Excel Ninja
Staff member
see attached
The offset was causing it to pickup the values at the bottom, so I shifted them down

#### Attachments

• 10.2 KB Views: 1

#### rajkenya1

##### Member
Thanok you once again Hui.

Cheers