IF, THEN HELP
in [Functions]
|
Prev: Format a dollar amount to text
Next: Formula issue.
From: Grammie on 29 Apr 2010 12:56 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 29 Apr 2010 13:16 =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 29 Apr 2010 13:38 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 29 Apr 2010 13:39 "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 29 Apr 2010 21:22
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. |