From: Grammie on
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.
From: Don Guillett on

=C3*IF(C3>36,1,IF(C3>=34,1.37,IF(C3>=32,1.38,1)))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Grammie" <bmoos1(a)yahoo.com> wrote in message
news:e58c9d63-9af1-463a-ab45-a8913c7f729c(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.

From: Eduardo on
Hi,
Let's assume you enter 32 in A1 and want the result in B1, so in B1 enter

=if(and(A1>=32,A1<=33.99),A1*1.38,if(and(A1>=34,A1<=35.99),A1*1.37))

you didn't explaing what happens if rate is outside of that ranges, if you
want a blank space use

=if(and(A1>=32,A1<=33.99),A1*1.38,if(and(A1>=34,A1<=35.99),A1*1.37,""))

"Grammie" wrote:

> 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.
> .
>
From: joeu2004 on
"Grammie" <bmoos1(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" <bmoos1(a)yahoo.com> wrote in message news:e58c9d63-9af1-463a-
ab45-a8913c7f729c(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.
From: Ashish Mathur on
Hi,

In B2:B3, enter 32 and 34. In C2:C3, enter 1.38 and 1.37. In B5, enter
32.23. In C5, use the following formula

=B5*vlookup(B5,$B$2:$C$3,2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Grammie" <bmoos1(a)yahoo.com> wrote in message
news:e58c9d63-9af1-463a-ab45-a8913c7f729c(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.

 |  Next  |  Last
Pages: 1 2
Prev: Format a dollar amount to text
Next: Formula issue.