Extending current formula

[EvansB2]

Hi there,

I currently have the following and I would like to add another statement.

=IF(MOD(Q$7,$H21)=0,$O21,0)

So, Q$7 represent a range of years from 1 to 20 and $H21 is an input cell that you input a year (service life of a product) and tells excel where to the drop the value in $O21 along the row, to the corresponding year along row 7.

I would like to add another column to the left of H (column I) that allows another input year that will be greater than the year in column H and represent the next service life of the product, i.e. the next replacement frequency, and will drop the same value in $O21 in the corresponding year along row 7.

Any assistance would be much appreciated, though understand my explanation might not be as clear as it should. I've had trouble using HTML maker on the company laptop!

Regards

Ben

 

[Simon4s]

Hi there,

I currently have the following and I would like to add another statement.

=IF(MOD(Q$7,$H21)=0,$O21,0)

So, Q$7 represent a range of years from 1 to 20 and $H21 is an input cell that you input a year (service life of a product) and tells excel where to the drop the value in $O21 along the row, to the corresponding year along row 7.

I would like to add another column to the left of H (column I) that allows another input year that will be greater than the year in column H and represent the next service life of the product, i.e. the next replacement frequency, and will drop the same value in $O21 in the corresponding year along row 7.

Any assistance would be much appreciated, though understand my explanation might not be as clear as it should. I've had trouble using HTML maker on the company laptop!

Regards

Ben

You want to Mod with i21 istead of h21???

if so, replace H21 with if(I21>h21,I21,h21). And of course, O21 will be shift to P21

 

[EvansB2]

Both i21 and h21 to be Mod


Ben

 

[EvansB2]

Hi,

I have copied down below what the spreadsheet look like on my screen and entered the values as they would be returned as if the formula would be in place. Hopefully, this will make more sense?

Regards

Ben
[TABLE="width: 538"]
<colgroup><col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" span="2"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" span="5"> <tbody>[TR]
[TD="width: 35, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 97, bgcolor: #D9D9D9"]A[/TD]
[TD="class: xl63, width: 103, bgcolor: #D9D9D9"]B[/TD]
[TD="class: xl63, width: 103, bgcolor: #D9D9D9"]c[/TD]
[TD="class: xl63, width: 76, bgcolor: #D9D9D9"]D[/TD]
[TD="class: xl63, width: 76, bgcolor: #D9D9D9"]E[/TD]
[TD="class: xl63, width: 76, bgcolor: #D9D9D9"]F[/TD]
[TD="class: xl63, width: 76, bgcolor: #D9D9D9"]G[/TD]
[TD="class: xl63, width: 76, bgcolor: #D9D9D9"]H[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #D9D9D9"]1[/TD]
[TD="class: xl65, width: 97, bgcolor: #9BC2E6"]Replacement Cost[/TD]
[TD="class: xl66, width: 103, bgcolor: #9BC2E6"]Service life of product (years)[/TD]
[TD="class: xl66, width: 103, bgcolor: #9BC2E6"]Frequency (years)[/TD]
[TD="class: xl67, bgcolor: #9BC2E6"]Year 1[/TD]
[TD="class: xl67, bgcolor: #9BC2E6"]Year 2[/TD]
[TD="class: xl67, bgcolor: #9BC2E6"]Year 3[/TD]
[TD="class: xl67, bgcolor: #9BC2E6"]Year 4[/TD]
[TD="class: xl67, bgcolor: #9BC2E6"]Year 5[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #D9D9D9"]2[/TD]
[TD="class: xl70, bgcolor: transparent"]£1,000[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]£1,000[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]£1,000[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #D9D9D9"]3[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[EvansB2]

Hi,

I have copied down below what the spreadsheet look like on my screen and entered the values as they would be returned as if the formula would be in place. Hopefully, this will make more sense?

Regards

Ben

[TABLE="width: 538"]
<colgroup><col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" span="2"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" span="5"> <tbody>[TR]
[TD="width: 35, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 97, bgcolor: #D9D9D9"]A[/TD]
[TD="class: xl65, width: 103, bgcolor: #D9D9D9"]B[/TD]
[TD="class: xl65, width: 103, bgcolor: #D9D9D9"]c[/TD]
[TD="class: xl65, width: 76, bgcolor: #D9D9D9"]D[/TD]
[TD="class: xl65, width: 76, bgcolor: #D9D9D9"]E[/TD]
[TD="class: xl65, width: 76, bgcolor: #D9D9D9"]F[/TD]
[TD="class: xl65, width: 76, bgcolor: #D9D9D9"]G[/TD]
[TD="class: xl65, width: 76, bgcolor: #D9D9D9"]H[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]1[/TD]
[TD="class: xl67, width: 97, bgcolor: #9BC2E6"]Replacement Cost[/TD]
[TD="class: xl68, width: 103, bgcolor: #9BC2E6"]Service life of product[/TD]
[TD="class: xl68, width: 103, bgcolor: #9BC2E6"]Frequency[/TD]
[TD="class: xl69, bgcolor: #9BC2E6"]Year 1[/TD]
[TD="class: xl69, bgcolor: #9BC2E6"]Year 2[/TD]
[TD="class: xl69, bgcolor: #9BC2E6"]Year 3[/TD]
[TD="class: xl69, bgcolor: #9BC2E6"]Year 4[/TD]
[TD="class: xl69, bgcolor: #9BC2E6"]Year 5[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]2[/TD]
[TD="class: xl72, bgcolor: transparent"]£1,000[/TD]
[TD="class: xl70, bgcolor: transparent"]2[/TD]
[TD="class: xl70, bgcolor: transparent"]3[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"]£1,000[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"]£1,000[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]3[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[EvansB2]

Hi Simon,

Can I raise this post again?

Regards

Ben