How do you combine 2 or more formulas with different totals?

[Trisha V via OfficeKB.com]

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?

 

[Henry]

Tricia,

How about
=INT((G9-1)/100)

Henry

 

[barrfly]

try somethin simple like this.

= int (A1/100)

let me know if this is on the right trac

 

[Biff]

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

 

[Dana DeLouis]

Hi. How about
=FLOOR(A1,100)

 

[Trisha V via OfficeKB.com]

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....

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 V via OfficeKB.com]

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?

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?

 

[Trisha V via OfficeKB.com]

thanks I tried that but it didnt work..

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?

 

[Dana DeLouis]

Oops. I didn't read the question correctly. You want 1, not 100...etc.
The other options are better than.

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

thanks I tried that but it didnt work..

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?

 

[Biff]

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

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....

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?

 

[RagDyer]

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!
==============================================

 

[Henry]

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

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?

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?

 

[Henry]

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
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....

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 V via OfficeKB.com]

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.

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]

 

[Trisha V via OfficeKB.com]

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?

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?

 

[Henry]

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

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.

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?

 

[Henry]

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
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?

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?

 

[Trisha V via OfficeKB.com]

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?

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]

 

[Henry]

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%.

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?

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?

 

[Trisha V via OfficeKB.com]

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.

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]