# Auto-populate spreadsheet data in highlighted cells

Ted Penner used Ask the Experts™
on
I need the cells in yellow to automatically populate using formulas.  Note that I must stay within Google Spreadsheets for this.

Assistance is greatly appreciated.
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Partner

Commented:
I guess no luck with google !!! With Excel be my guest.
gowflow
Partner

Commented:
Not familiar with google Spreadsheets but can assist if you know how to put formulas in google.

For result in I2 put this formula
``````=IF(DAY(H2)>DAY(A2),">"&DATEDIF(A2,H2,"m")&" Months",IF(DAY(H2)=DAY(A2),"="&DATEDIF(A2,H2,"m")&" Months"))
``````

For result in J2 kindly give me what it is supposed to show

Regards
Gowflow
Software Engineer

Commented:
The solution for I2 works but needs to be represented in terms of an absolute value of years, instead of the less than # of months thing.

Then J2 would be the same calculation as what is currently in G2 but per year as opposed to the total % Cost of Put Insurance.
Partner

Commented:
ok here it is for I2
``````=IF(DAY(H2)>DAY(A2),">"&DATEDIF(A2,H2,"y")&" Years",IF(DAY(H2)=DAY(A2),"="&DATEDIF(A2,H2,"y")&" Years"))
``````

Try this in J2
``````=(F2/DATEDIF(A2,H2,"y"))/E2
``````

gowflow
Software Engineer

Commented:
That's closer but I2 should show 2.x yrs for instance, and J2, in this case, should show 7% per yr.
Partner
Commented:
ok I see what you want lets try this combination then
I2
``````=DATEDIF(A2,H2,"y")&"."&DATEDIF(A2,H2,"ym")
``````

J2
``````=(F2/I2)/E2
``````

Just format both I2 and J2 to show 2 digits decimal points and tell me if the result is accurate. (You can round it after)

gowflow
Software Engineer

Commented:
Perfect!

Thank you.
Partner

Commented: