Return Value Based on Two Others

  • Thread starter Thread starter Pablo
  • Start date Start date
P

Pablo

I'm pretty sure I'm going to wind up with a very long formula here...

I have a cell that shows payback on an energy efficiency measure.
Cost / savings = payback. I have the cell formatting to add "years"
to any value.

This gives me a decimal usually. .1 years...

I would like to convert the fraction of a year into months. So .5
years would return 6 months. If you divide the months 1, 2, 3, etc.
by 12 you get a series of factions so if you wanted to say some
payback was 1 month the value would be 0 - .085. A value between .086
- .25 would return 2 months, 1.086 - 1.25 would be 1 year 2 months,
etc..

Is there a way to do this without a huge formula with an infinite
number of IFs in it?
 
Suppose your fractional values are in column E, starting with E1. Put
this formula in F1:

=IF(E1<1,"",INT(E1)&" year"&IF(INT(E1)=1," ","s
"))&IF(E1=INT(E1),"",ROUND((E1-INT(E1))*12,0)&" month"&IF(E1-
INT(E1)<=0.1,"","s"))

and copy it down. Here's some examples of the output:

0.1 1 month
0.2 2 months
0.3 4 months
0.4 5 months
0.5 6 months
0.6 7 months
0.7 8 months
0.8 10 months
0.9 11 months
1.0 1 year
1.1 1 year 1 month
1.2 1 year 2 months
1.3 1 year 4 months
1.4 1 year 5 months
1.5 1 year 6 months
1.6 1 year 7 months
1.7 1 year 8 months
1.8 1 year 10 months
1.9 1 year 11 months
2.0 2 years
2.1 2 years 1 month
2.2 2 years 2 months
2.3 2 years 4 months
2.4 2 years 5 months
2.5 2 years 6 months
2.6 2 years 7 months
2.7 2 years 8 months
2.8 2 years 10 months
2.9 2 years 11 months

Hope this helps.

Pete
 
Back
Top