Auto-populate spreadsheet data in highlighted cells

Ted Penner
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.
https://docs.google.com/spreadsheets/d/164JRaQDrsiwXlDr8sJWed8RqoPu59YLG_a2-BgYfZaM/edit#gid=0

Assistance is greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I guess no luck with google !!! With Excel be my guest.
gowflow
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"))

Open in new window


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

Regards
Gowflow
Ted PennerSoftware Engineer

Author

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.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

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"))

Open in new window


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

Open in new window


gowflow
Ted PennerSoftware Engineer

Author

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

Open in new window


J2
=(F2/I2)/E2

Open in new window


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
Ted PennerSoftware Engineer

Author

Commented:
Perfect!

Thank you.
Great Glad I could help.

If you need more help please do not hesitate to message me
gowflow

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial