Im working on a formula that looks somewhat like this:
=IF(G9>100,1,IF(G9>200,2,IF(G9>300,3,)))
but it isnt working...what I need is something that says...IF (G9) IS GREATER
THAN 100 IT WILL EQUAL 1 AND IF GREATER THAN 200, IT WILL EQUAL 2 AND GREATER
THAN 300, ETC ETC ETC...TILL I GET TO 1000 AND 10.
WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
try somethin simple like this.
= int (A1/100)
let me know if this is on the right track
Barrfly
Tricia,
How about
=INT((G9-1)/100)
Henry
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Im working on a formula that looks somewhat like this:
>
> =IF(G9>100,1,IF(G9>200,2,IF(G9>300,3,)))
>
> but it isnt working...what I need is something that says...IF (G9) IS
> GREATER
> THAN 100 IT WILL EQUAL 1 AND IF GREATER THAN 200, IT WILL EQUAL 2 AND
> GREATER
> THAN 300, ETC ETC ETC...TILL I GET TO 1000 AND 10.
>
> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
Hi!
> =IF(G9>100,1,IF(G9>200,2,IF(G9>300,3,)))
If all you needed to check were the 3 conditions in your sample then you
simply need to reverse the logic:
=IF(G9>300,3,IF(G9>200,2,IF(G9>100,1,"not defined")))
Since you have 10 conditions you want to test for, using IF won't work.
What do you want to happen if G9 < 100?
What do you want to happen if G9 = 100, 200, 300 etc.?
Biff
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Im working on a formula that looks somewhat like this:
>
> =IF(G9>100,1,IF(G9>200,2,IF(G9>300,3,)))
>
> but it isnt working...what I need is something that says...IF (G9) IS
> GREATER
> THAN 100 IT WILL EQUAL 1 AND IF GREATER THAN 200, IT WILL EQUAL 2 AND
> GREATER
> THAN 300, ETC ETC ETC...TILL I GET TO 1000 AND 10.
>
> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
Hi. How about
=FLOOR(A1,100)
--
Dana DeLouis
Win XP & Office 2003
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Im working on a formula that looks somewhat like this:
>
> =IF(G9>100,1,IF(G9>200,2,IF(G9>300,3,)))
>
> but it isnt working...what I need is something that says...IF (G9) IS
> GREATER
> THAN 100 IT WILL EQUAL 1 AND IF GREATER THAN 200, IT WILL EQUAL 2 AND
> GREATER
> THAN 300, ETC ETC ETC...TILL I GET TO 1000 AND 10.
>
> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
Biff, thanks for your helping me out....what Im working on is a contest where
depending on how much someone sells they get certain pulls out of a hat with
money on them....If they sell from $100 to $199, they get 1 pull, $200-$299
they get 2 pulls, etc etc etc ... and they can only get up to 10 pulls...Hope
this helps....
Biff wrote:
>Hi!
>
>> =IF(G9>100,1,IF(G9>200,2,IF(G9>300,3,)))
>
>If all you needed to check were the 3 conditions in your sample then you
>simply need to reverse the logic:
>
>=IF(G9>300,3,IF(G9>200,2,IF(G9>100,1,"not defined")))
>
>Since you have 10 conditions you want to test for, using IF won't work.
>
>What do you want to happen if G9 < 100?
>
>What do you want to happen if G9 = 100, 200, 300 etc.?
>
>Biff
>
>> Im working on a formula that looks somewhat like this:
>>
>[quoted text clipped - 7 lines]
>>
>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
OH my goodness, that's actually working...i do have a couple of questions
though. What is INT? and what does it stand for? and how come on the value
with $0 I get a negative number instead of 0?
Henry wrote:
>Tricia,
>
>How about
>=INT((G9-1)/100)
>
>Henry
>
>> Im working on a formula that looks somewhat like this:
>>
>[quoted text clipped - 7 lines]
>>
>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
thanks I tried that but it didnt work..
Dana DeLouis wrote:
>Hi. How about
> =FLOOR(A1,100)
>
>> Im working on a formula that looks somewhat like this:
>>
>[quoted text clipped - 7 lines]
>>
>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
Oops. I didn't read the question correctly. You want 1, not 100...etc.
The other options are better than.
>>> WHAT AM I DOING WRONG? ...
You are trying to use 10 IF's, but you are running into the limit of 7 IF's
for Excel. Here's a copy from Help on "IF"
...."Up to seven IF functions can be nested as value_if_true and
value_if_false arguments to construct more elaborate tests."
Hope that explains the problem. ;>)
--
Dana DeLouis
Win XP & Office 2003
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> thanks I tried that but it didnt work..
>
> Dana DeLouis wrote:
>>Hi. How about
>> =FLOOR(A1,100)
>>
>>> Im working on a formula that looks somewhat like this:
>>>
>>[quoted text clipped - 7 lines]
>>>
>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
Hi!
Create a small table somewhere on you sheet:
0 0
100 1
200 2
300 3
400 4
500 5
600 6
700 7
800 8
900 9
1000 10
Assume this table is in the range G1:H11
Cell A1 holds the sales amount:
A1 = 219
=VLOOKUP(A1,G1:H11,2,1)
Biff
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Biff, thanks for your helping me out....what Im working on is a contest
> where
> depending on how much someone sells they get certain pulls out of a hat
> with
> money on them....If they sell from $100 to $199, they get 1 pull,
> $200-$299
> they get 2 pulls, etc etc etc ... and they can only get up to 10
> pulls...Hope
> this helps....
>
> Biff wrote:
>>Hi!
>>
>>> =IF(G9>100,1,IF(G9>200,2,IF(G9>300,3,)))
>>
>>If all you needed to check were the 3 conditions in your sample then you
>>simply need to reverse the logic:
>>
>>=IF(G9>300,3,IF(G9>200,2,IF(G9>100,1,"not defined")))
>>
>>Since you have 10 conditions you want to test for, using IF won't work.
>>
>>What do you want to happen if G9 < 100?
>>
>>What do you want to happen if G9 = 100, 200, 300 etc.?
>>
>>Biff
>>
>>> Im working on a formula that looks somewhat like this:
>>>
>>[quoted text clipped - 7 lines]
>>>
>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
How about:
=CHOOSE(--LEFT(G9),1,2,3,4,5,6,7,8,9,10)
OR
If you have numbers larger then 1,000 and smaller then 100, try this:
=CHOOSE((LEN(G9)>=4)*10+(LEN(G9)=3)*LEFT(G9),1,2,3,4,5,6,7,8,9,10)
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Im working on a formula that looks somewhat like this:
>
> =IF(G9>100,1,IF(G9>200,2,IF(G9>300,3,)))
>
> but it isnt working...what I need is something that says...IF (G9) IS
GREATER
> THAN 100 IT WILL EQUAL 1 AND IF GREATER THAN 200, IT WILL EQUAL 2 AND
GREATER
> THAN 300, ETC ETC ETC...TILL I GET TO 1000 AND 10.
>
> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
Trisha,
INT is the whole number part of the answer (without decimals)
So, 123/100 = 1.23 but INT(123/100) = 1
As you wanted numbers *greater* than 100 to = 1 and 100/100 =1, I subtracted
1 from the value of G9 to make that work
(100 - 1)/100 = 0.99
INT (0.99) = 0
If you want 0 to 99 = 0, 100 to 199 = 1 and 200 to 299 =2 etc. then take out
the -1
If you still want 100 to give you 0 and 0 to give you a zero answer then
=IF(G9 = 0,0,INT((G9-1)/100))
should do you.
Translation:
If G9=0 THEN put in 0, ELSE put in the calculated number.
Henry
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> OH my goodness, that's actually working...i do have a couple of questions
> though. What is INT? and what does it stand for? and how come on the value
> with $0 I get a negative number instead of 0?
>
> Henry wrote:
>>Tricia,
>>
>>How about
>>=INT((G9-1)/100)
>>
>>Henry
>>
>>> Im working on a formula that looks somewhat like this:
>>>
>>[quoted text clipped - 7 lines]
>>>
>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
I got help for this one finally...thank you though...maybe you can help me
with another one..if someone increases an amount by %25 then they get a point,
if they increase it by %50 they get 2 points, by %75, 3 points, and %100, 4
points.... can you figure that one out for me please?
RagDyer wrote:
>How about:
>
>=CHOOSE(--LEFT(G9),1,2,3,4,5,6,7,8,9,10)
>OR
>If you have numbers larger then 1,000 and smaller then 100, try this:
>
>=CHOOSE((LEN(G9)>=4)*10+(LEN(G9)=3)*LEFT(G9),1,2,3,4,5,6,7,8,9,10)
>
>> Im working on a formula that looks somewhat like this:
>>
>[quoted text clipped - 5 lines]
>>
>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1
Henry, I want to thank you so much for your help. Its funny you have my
husbands name... thanks again..I have another question.. how would I do a
similar formula if the rules for the contest that Im working on read like
this...If you sell $800 you get 2 pulls (out of a hat) and each additional
$100 you sell you get an additional $100 to a max of 10.
Henry wrote:
>Trish,
>
>This is different from what you originally asked.
>
>>100 means 101 upwards.
>Now you're saying >99 (or =>100) =100 upwards.
>
>If this is the case then =INT(G9/100) will do you.
>
>To limit it to a Max of 10 pulls, then
>
>=IF(G9>999,10,INT(G9/100))
>
>Henry
>
>> Biff, thanks for your helping me out....what Im working on is a contest
>> where
>[quoted text clipped - 28 lines]
>>>>
>>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1
Trish,
This is different from what you originally asked.
>100 means 101 upwards.
Now you're saying >99 (or =>100) =100 upwards.
If this is the case then =INT(G9/100) will do you.
To limit it to a Max of 10 pulls, then
=IF(G9>999,10,INT(G9/100))
Henry
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Biff, thanks for your helping me out....what Im working on is a contest
> where
> depending on how much someone sells they get certain pulls out of a hat
> with
> money on them....If they sell from $100 to $199, they get 1 pull,
> $200-$299
> they get 2 pulls, etc etc etc ... and they can only get up to 10
> pulls...Hope
> this helps....
>
> Biff wrote:
>>Hi!
>>
>>> =IF(G9>100,1,IF(G9>200,2,IF(G9>300,3,)))
>>
>>If all you needed to check were the 3 conditions in your sample then you
>>simply need to reverse the logic:
>>
>>=IF(G9>300,3,IF(G9>200,2,IF(G9>100,1,"not defined")))
>>
>>Since you have 10 conditions you want to test for, using IF won't work.
>>
>>What do you want to happen if G9 < 100?
>>
>>What do you want to happen if G9 = 100, 200, 300 etc.?
>>
>>Biff
>>
>>> Im working on a formula that looks somewhat like this:
>>>
>>[quoted text clipped - 7 lines]
>>>
>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
Trisha,
=IF(G9=>1600,10,IF(G9<800,0,INT(G9/100)-6))
$1599.99 will give you 9 pulls
$1600 or more will give you 10 pulls
$799.99 or less will give you 0 pulls
$800 to $899.99 will give you 2 pulls
$900 to $999.99 will give you 3 pulls
etc.
Henry
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Henry, I want to thank you so much for your help. Its funny you have my
> husbands name... thanks again..I have another question.. how would I do a
> similar formula if the rules for the contest that Im working on read like
> this...If you sell $800 you get 2 pulls (out of a hat) and each additional
> $100 you sell you get an additional $100 to a max of 10.
>
> Henry wrote:
>>Trish,
>>
>>This is different from what you originally asked.
>>
>>>100 means 101 upwards.
>>Now you're saying >99 (or =>100) =100 upwards.
>>
>>If this is the case then =INT(G9/100) will do you.
>>
>>To limit it to a Max of 10 pulls, then
>>
>>=IF(G9>999,10,INT(G9/100))
>>
>>Henry
>>
>>> Biff, thanks for your helping me out....what Im working on is a contest
>>> where
>>[quoted text clipped - 28 lines]
>>>>>
>>>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200508/1
Trisha,
Assume your starting figure is in A1 & your final figure is in B1.
In C1 put
=IF(B1=>A1*2,4,IF(B1=>A1*1.75,3,IF(B1=>A1*1.5,2,IF(B1=>A1*1.25,1,0))))
Henry
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> I got help for this one finally...thank you though...maybe you can help me
> with another one..if someone increases an amount by %25 then they get a
> point,
> if they increase it by %50 they get 2 points, by %75, 3 points, and %100,
> 4
> points.... can you figure that one out for me please?
>
> RagDyer wrote:
>>How about:
>>
>>=CHOOSE(--LEFT(G9),1,2,3,4,5,6,7,8,9,10)
>>OR
>>If you have numbers larger then 1,000 and smaller then 100, try this:
>>
>>=CHOOSE((LEN(G9)>=4)*10+(LEN(G9)=3)*LEFT(G9),1,2,3,4,5,6,7,8,9,10)
>>
>>> Im working on a formula that looks somewhat like this:
>>>
>>[quoted text clipped - 5 lines]
>>>
>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200508/1
HMMM....I ALREADY HAVE THE C1 INFO SO HOW WOULD THE FORMULA CHANGE NOW IF
THEY GET UP TO 10 POINTS. MEANING INCREMENTS OF 25%.
HERE'S ANOTHER ONE...THIS HAS TO BE SIMPLER.. EVERY 3 NEW CUSTOMERS THE SELL
TO THEY GET A POINT UP TO 10 POINTS... HOW WOULD I DO THAT FORMULA?
Henry wrote:
>Trisha,
>
>Assume your starting figure is in A1 & your final figure is in B1.
>In C1 put
>=IF(B1=>A1*2,4,IF(B1=>A1*1.75,3,IF(B1=>A1*1.5,2,IF(B1=>A1*1.25,1,0))))
>
>Henry
>
>> I got help for this one finally...thank you though...maybe you can help me
>> with another one..if someone increases an amount by %25 then they get a
>[quoted text clipped - 16 lines]
>>>>
>>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1
Tricia,
First one:
((B1-A1)/A1 )*100 will give the percentage increase.
((B1-A1)/A1)* 4 will give 1 for 25%, 2 for 50%, etc.
INT(((B1-A1)/A1)* 4 ) makes sure you only get whole numbers.
=IF(INT(((B1-A1)/A1)* 4 )>10,10,INT(((B1-A1)/A1)* 4 )) limits it to max 10.
Second one:
Depends what you mean by "New customers" and how you are recording these.
Is someone they haven't sold to for 3 months a "New Customer"?
Is someone they haven't sold to for 6 months a "New Customer"?
Is the number of new customers recorded in a cell somewhere?
Say it's in D1
In E1, put
IF(INT(D1/3)>10,10,INT(D1/3))
Henry
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> HMMM....I ALREADY HAVE THE C1 INFO SO HOW WOULD THE FORMULA CHANGE NOW IF
> THEY GET UP TO 10 POINTS. MEANING INCREMENTS OF 25%.
>
> HERE'S ANOTHER ONE...THIS HAS TO BE SIMPLER.. EVERY 3 NEW CUSTOMERS THE
> SELL
> TO THEY GET A POINT UP TO 10 POINTS... HOW WOULD I DO THAT FORMULA?
>
>
> Henry wrote:
>>Trisha,
>>
>>Assume your starting figure is in A1 & your final figure is in B1.
>>In C1 put
>>=IF(B1=>A1*2,4,IF(B1=>A1*1.75,3,IF(B1=>A1*1.5,2,IF(B1=>A1*1.25,1,0))))
>>
>>Henry
>>
>>> I got help for this one finally...thank you though...maybe you can help
>>> me
>>> with another one..if someone increases an amount by %25 then they get a
>>[quoted text clipped - 16 lines]
>>>>>
>>>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200508/1
You might have this right for what im looking for... this is what i was
working on..we sell advertising ads for the yellow pages. last year, they had
a certain ad that (lets say) was $100..this year the sales rep upsold them
$125 making that a %25 increase...i said this before..i know. the thing is I
already have the variance that calculates the % so the formula in that case
would change? right?
the same contest with the pulls is the one that has a lot of formulas needed.
a new customer would be someone that never had an ad in the yellow pages. i
just have a count of how many new customers there were for week. so if i have
3 new customers in a week, the sales rep gets a pull (or a point)...6 new
customer would = 2 pulls, 9 new customers=3, etc etc..with a max of 10 pulls.
Henry wrote:
>Tricia,
>
>First one:
>
>((B1-A1)/A1 )*100 will give the percentage increase.
>((B1-A1)/A1)* 4 will give 1 for 25%, 2 for 50%, etc.
>INT(((B1-A1)/A1)* 4 ) makes sure you only get whole numbers.
>=IF(INT(((B1-A1)/A1)* 4 )>10,10,INT(((B1-A1)/A1)* 4 )) limits it to max 10.
>
>Second one:
>Depends what you mean by "New customers" and how you are recording these.
>Is someone they haven't sold to for 3 months a "New Customer"?
>Is someone they haven't sold to for 6 months a "New Customer"?
>
>Is the number of new customers recorded in a cell somewhere?
>
>Say it's in D1
>In E1, put
>
>IF(INT(D1/3)>10,10,INT(D1/3))
>
>Henry
>
>> HMMM....I ALREADY HAVE THE C1 INFO SO HOW WOULD THE FORMULA CHANGE NOW IF
>> THEY GET UP TO 10 POINTS. MEANING INCREMENTS OF 25%.
>[quoted text clipped - 17 lines]
>>>>>>
>>>>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1
I TRIED THE SECOND ONE AND IT WORKED....WOW, THANK YOU...
Henry wrote:
>Tricia,
>
>First one:
>
>((B1-A1)/A1 )*100 will give the percentage increase.
>((B1-A1)/A1)* 4 will give 1 for 25%, 2 for 50%, etc.
>INT(((B1-A1)/A1)* 4 ) makes sure you only get whole numbers.
>=IF(INT(((B1-A1)/A1)* 4 )>10,10,INT(((B1-A1)/A1)* 4 )) limits it to max 10.
>
>Second one:
>Depends what you mean by "New customers" and how you are recording these.
>Is someone they haven't sold to for 3 months a "New Customer"?
>Is someone they haven't sold to for 6 months a "New Customer"?
>
>Is the number of new customers recorded in a cell somewhere?
>
>Say it's in D1
>In E1, put
>
>IF(INT(D1/3)>10,10,INT(D1/3))
>
>Henry
>
>> HMMM....I ALREADY HAVE THE C1 INFO SO HOW WOULD THE FORMULA CHANGE NOW IF
>> THEY GET UP TO 10 POINTS. MEANING INCREMENTS OF 25%.
>[quoted text clipped - 17 lines]
>>>>>>
>>>>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1
CAS SOMEONE WITH THE FIRST ONE PLEASE...
Trisha V wrote:
>I TRIED THE SECOND ONE AND IT WORKED....WOW, THANK YOU...
>
>>Tricia,
>>
>[quoted text clipped - 24 lines]
>>>>>>>
>>>>>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
--
Message posted via http://www.officekb.com
Trisha,
"CAS SOMEONE WITH THE FIRST ONE PLEASE..."
Sorry, does not compute! Syntax error.
Henry
"Trisha V via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> CAS SOMEONE WITH THE FIRST ONE PLEASE...
>
> Trisha V wrote:
>>I TRIED THE SECOND ONE AND IT WORKED....WOW, THANK YOU...
>>
>>>Tricia,
>>>
>>[quoted text clipped - 24 lines]
>>>>>>>>
>>>>>>>> WHAT AM I DOING WRONG? CAN SOMEONE PLEASE HELP ME?
>
>
> --
> Message posted via http://www.officekb.com
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks