Prev: Format a dollar amount to text
Next: Formula issue.
From: Grammie on 5 May 2010 18:03
On Apr 29, 10:39 am, joeu2004 <joeu2...(a)hotmail.com> wrote:
> "Grammie" <bmo...(a)yahoo.com> wrote:
> > If the rate is between 32.00 and 33.99 then the cost
> > is multiplied by 38% (1.38).
> > If the rate is between 34.00 and 35.99 then the cost
> > is multiplied by 37% (1.37)
>
> > I want excel to automatically give me the resulting
> > figure when I put in the rate, so that when it sees
> > $32.23, it knows to multiply by 1.38 and automatically
> > give me the resulting value of 44.32.
>
> (Note:  32.23*1.38 is about 44.48, not 44.32.  If the rate is really
> 1.375%, do not write 38%.)
>
> If you have only those two ranges and rates, then a simple IF
> expression would suffice, to wit (where A1 contains the original
> cost):
>
> =ROUND(A1*IF(A1<34,1.38,1.37), 2)
>
> ROUND is necessary to ensure a dollars-and-cents result without
> fractional pennies.
>
> But I suspect you have many more ranges and rates.  In that case, it
> would be better to set up lookup function.  There are several ways to
> do that.  One way:
>
> =ROUND(A1*LOOKUP(A1,{32,34},{1.38,1.37}), 2)
>
> ----- original message -----
>
> "Grammie" <bmo...(a)yahoo.com> wrote in message news:e58c9d63-9af1-463a-
>
> ab45-a8913c7f7...(a)r21g2000prr.googlegroups.com...
>
>
>
> >I have a rate chart which covers different values, such as:
>
> > If the rate is between 32.00 and 33.99 then the cost is multiplied by
> > 38% (1.38).
> > If the rate is between 34.00 and 35.99 then the cost is multiplied by
> > 37% (1.37)
>
> > I want excel to automatically give me the resulting figure when I put
> > in the rate, so that when it sees $32.23, it knows to multiply by 1.38
> > and automatically give me the resulting value of 44.32.
>
> > Anyone out there that can help, I appreciate it.- Hide quoted text -
>
> - Show quoted text -

Thank you for your answer. I didn't need to round the number and you
were correct the actual is 1.375 and it makes a difference.
First  |  Prev  | 
Pages: 1 2
Prev: Format a dollar amount to text
Next: Formula issue.