Need Help With Spreadsheet

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I need assistance. The task that I am trying to perform
seems relatively simple but I can't figure it out. I have
developed cells that calculate number of days between two
dates. The result returns just fine. I then developed a
cell that divides this number by 365 to calculate the
number of years and returns a number with a decimal point
for partial years. Honestly, I only need it to return a
whole year number without rounding up or down but this is
OK as well. The real problem I am having is getting this
result to return a specified number based on the result of
the year calculation. To be specific, there are different
accrual rates based on the number of years, For instance,
if the whole year number return is 3, I would like it to
display .06574 but if the whole number returned is 7, I
would like it to display .09765. There are 6 different
variables based on the number of years returned. Any
sample formulas and cell formatting would be appreciated.

Thanks
 
Rick said:
I need assistance. The task that I am trying to perform
seems relatively simple but I can't figure it out. I have
developed cells that calculate number of days between two
dates. The result returns just fine. I then developed a
cell that divides this number by 365 to calculate the
number of years and returns a number with a decimal point
for partial years. Honestly, I only need it to return a
whole year number without rounding up or down but this is
OK as well. The real problem I am having is getting this
result to return a specified number based on the result of
the year calculation. To be specific, there are different
accrual rates based on the number of years, For instance,
if the whole year number return is 3, I would like it to
display .06574 but if the whole number returned is 7, I
would like it to display .09765. There are 6 different
variables based on the number of years returned. Any
sample formulas and cell formatting would be appreciated.

Thanks
Click to expand...

You could use
=ROUND(YourFormula,0)
or
=TRUNC(YourFormula)
to get an integer year value for your formula.

I assume you mean that the number of years could be any of 1, 2, 3, 4, 5 or
6 and that you want to get the appropriate one of six accrual rates. You can
do this with a VLOOKUP.
Put the numbers 1, 2, 3, 4, 5 and 6 in (say) A1:A6 and the corresponding
accrual rates in B1:B6. If your integer year result were in C1, this formula
will return the appropriate accrual rate:
=VLOOKUP(C1,$A$1:$B$6,2,0)
 
Back
Top