Excel 2002

[John]

I am trying to create 2 formulas,

I can get this part to work =IF(Q:Q<=5000,Q:Q*I:I,I:I*5000)

but I can't phrase the first part so excel can understand what I am trying
to accomplish. This first condition (=If I:I is < than or > than but not =
to 1) must be met before going any further.

1st formula:
IF I:I is < than or > than but not = to 1, then IF(Q:Q<=5000,Q:Q*I:I,I:I*5000)

2nd formula:
IF I:I is not < or > but = to 1, then IF(Q:Q<=5000,Q:Q*I:I,I:I*5000)

 

[Elkar]

I'm not sure I understand what you're trying to do. Do you want to add the
products of I and Q when I is not equal to 1?

Maybe this is what you want:

=SUM(IF(I1:I99<>1,MIN(5000,Q1:Q99)*I1:I99))

This is an array formula. It must be committed with CTRL-SHIFT-ENTER
instead of just enter. If done properly, the formula should be enclosed with
{ }.

HTH
Elkar

 

[smartin]

I am trying to create 2 formulas,

I can get this part to work =IF(Q:Q<=5000,Q:Q*I:I,I:I*5000)

but I can't phrase the first part so excel can understand what I am trying
to accomplish. This first condition (=If I:I is < than or > than but not =
to 1) must be met before going any further.

1st formula:
IF I:I is < than or > than but not = to 1, then IF(Q:Q<=5000,Q:Q*I:I,I:I*5000)

2nd formula:
IF I:I is not < or > but = to 1, then IF(Q:Q<=5000,Q:Q*I:I,I:I*5000)

You could try one formula using nested IFs:
=IF(I:I<>1,IF(Q:Q<=5000,I:I*Q:Q,I:I*5000),[formula if I=1])

In your example you do not differentiate between what should happen if
I<>1 vs. I=1. Was this a typo?

 

[John]

Elkar'

I'll try to explain what I am trying to do.

I am making a spreadsheet for a sliding scale tax
column "I" contains the surtax rate which is usually 1 %
column "Q" will contain an amount less than, equal to, or greater than 5000
column "Z" will return the result
example:
"I" = 1%, Q= 4000, "Z" = 40
"I" = 1%, Q= 5000, "Z" = 50
"I" = 1%, Q= 6000, "Z" = 50

As you can see "Z" can never be more than 50 @ 1% with this formula
=IF(Q:Q<=5000,Q:Q*I:I,I:I*5000) so this part works fine.

Now what I am trying to do is this:

I want the result to show up in column "Z" if column "I" is less than or
greater than 1%
But I want the result to show up in column "AC" if column "I" is equal to 1%
The result could show up in only one column at a time

"I" = 1%, Q= 4000, "Z" = 40
"I" = 1%, Q= 5000, "Z" = 50
"I" = 1%, Q= 6000, "Z" = 50
"I" = .5%, Q= 4000, AC" = 20
"I" = .5%, Q= 5000, AC " = 25
"I" = .5%, Q= 6000, AC" = 25
"I" = 1.5%, Q= 4000, AC" = 60
"I" = 1.5%, Q= 5000, AC" = 75
"I" = 1.5%, Q= 6000, AC" = 75

 

[John]

"smartin"

I'll try to explain what I am trying to do.

I am making a spreadsheet for a sliding scale tax
column "I" contains the surtax rate which is usually 1 %
column "Q" will contain an amount less than, equal to, or greater than 5000
column "Z" will return the result
example:
"I" = 1%, Q= 4000, "Z" = 40
"I" = 1%, Q= 5000, "Z" = 50
"I" = 1%, Q= 6000, "Z" = 50

As you can see "Z" can never be more than 50 @ 1% with this formula
=IF(Q:Q<=5000,Q:Q*I:I,I:I*5000) so this part works fine.

Now what I am trying to do is this:

I want the result to show up in column "Z" if column "I" is less than or
greater than 1%
But I want the result to show up in column "AC" if column "I" is equal to 1%
The result could show up in only one column at a time

"I" = 1%, Q= 4000, "Z" = 40
"I" = 1%, Q= 5000, "Z" = 50
"I" = 1%, Q= 6000, "Z" = 50
"I" = .5%, Q= 4000, AC" = 20
"I" = .5%, Q= 5000, AC " = 25
"I" = .5%, Q= 6000, AC" = 25
"I" = 1.5%, Q= 4000, AC" = 60
"I" = 1.5%, Q= 5000, AC" = 75
"I" = 1.5%, Q= 6000, AC" = 75

 

[smartin]

"smartin"

I'll try to explain what I am trying to do.

I am making a spreadsheet for a sliding scale tax
column "I" contains the surtax rate which is usually 1 %
column "Q" will contain an amount less than, equal to, or greater than 5000
column "Z" will return the result
example:
"I" = 1%, Q= 4000, "Z" = 40
"I" = 1%, Q= 5000, "Z" = 50
"I" = 1%, Q= 6000, "Z" = 50

As you can see "Z" can never be more than 50 @ 1% with this formula
=IF(Q:Q<=5000,Q:Q*I:I,I:I*5000) so this part works fine.

Now what I am trying to do is this:

I want the result to show up in column "Z" if column "I" is less than or
greater than 1%
But I want the result to show up in column "AC" if column "I" is equal to 1%
The result could show up in only one column at a time

"I" = 1%, Q= 4000, "Z" = 40
"I" = 1%, Q= 5000, "Z" = 50
"I" = 1%, Q= 6000, "Z" = 50
"I" = .5%, Q= 4000, AC" = 20
"I" = .5%, Q= 5000, AC " = 25
"I" = .5%, Q= 6000, AC" = 25
"I" = 1.5%, Q= 4000, AC" = 60
"I" = 1.5%, Q= 5000, AC" = 75
"I" = 1.5%, Q= 6000, AC" = 75

Is this closer?:

If I:I is whole numbers,

In column zed,
=IF(I:I<>1,IF(Q:Q<=5000,Q:Q*I:I/100,I:I*5000/100),"")

In column AC,
=IF(I:I=1,IF(Q:Q<=5000,Q:Q*I:I/100,I:I*5000/100),"")

Else

In column zed,
=IF(I:I<>0.01,IF(Q:Q<=5000,Q:Q*I:I,I:I*5000),"")

In column AC,
=IF(I:I=0.01,IF(Q:Q<=5000,Q:Q*I:I,I:I*5000),"")

 

[Ron Rosenfeld]

"smartin"

I'll try to explain what I am trying to do.

I am making a spreadsheet for a sliding scale tax
column "I" contains the surtax rate which is usually 1 %
column "Q" will contain an amount less than, equal to, or greater than 5000
column "Z" will return the result
example:
"I" = 1%, Q= 4000, "Z" = 40
"I" = 1%, Q= 5000, "Z" = 50
"I" = 1%, Q= 6000, "Z" = 50

As you can see "Z" can never be more than 50 @ 1% with this formula
=IF(Q:Q<=5000,Q:Q*I:I,I:I*5000) so this part works fine.

Now what I am trying to do is this:

I want the result to show up in column "Z" if column "I" is less than or
greater than 1%
But I want the result to show up in column "AC" if column "I" is equal to 1%
The result could show up in only one column at a time

"I" = 1%, Q= 4000, "Z" = 40
"I" = 1%, Q= 5000, "Z" = 50
"I" = 1%, Q= 6000, "Z" = 50
"I" = .5%, Q= 4000, AC" = 20
"I" = .5%, Q= 5000, AC " = 25
"I" = .5%, Q= 6000, AC" = 25
"I" = 1.5%, Q= 4000, AC" = 60
"I" = 1.5%, Q= 5000, AC" = 75
"I" = 1.5%, Q= 6000, AC" = 75

Your description and your example are not the same.

In your description, you want the result in col Z if col I is not equal to 1%;
whereas in your example you have it the other way.

In any event, for the column where you want the result when I = 1%, assuming
your data begins in Row 1 (adjust the formula as necessary):

=IF(I1=1%,I1*MIN(Q1,5000),"")

Fill down as far as required.

For the column where you want the result when Col I is greater than or less
than 1%:

=IF(I1<>1%,I1*MIN(Q1,5000),"")

--ron

 

[John]

smartin,

Thanks a lot. your second example works, can't beleive it was that simple.
I had tried that but with the exception of I had "1" where you have ".01"
that made all the difference. It appeared that excel couldn't recognize <> or
= to "1" I guess it interpreted "1" as "100"
It was pointed out to me that I had reversed my example IE: results for "z"
/ "AC" columns but you figured it out anyway, "Good Job" I appreciate the
help, I had exhausted my trial and error possibilities. I am curious why the
"" at the end of the formula?

 

[smartin]

smartin,

Thanks a lot. your second example works, can't beleive it was that simple.
I had tried that but with the exception of I had "1" where you have ".01"
that made all the difference. It appeared that excel couldn't recognize <> or
= to "1" I guess it interpreted "1" as "100"

Correct. Formatting a column as % does not change the value in that
column. IOW, 1% = 0.01, and 1 = 100%.

It was pointed out to me that I had reversed my example IE: results for "z"
/ "AC" columns but you figured it out anyway, "Good Job" I appreciate the
help, I had exhausted my trial and error possibilities. I am curious why the
"" at the end of the formula?

The "" is there just to return a blank if the first IF fails. If for
example you wrote

=IF(I:I<>0.01,IF(Q:Q<=5000,Q:Q*I:I,I:I*5000))

You would get FALSE where I:I=0.01.

Another option for handling false IF results, which is particularly
helpful to remember if you will be charting the results, is to use NA():

=IF(I:I<>0.01,IF(Q:Q<=5000,Q:Q*I:I,I:I*5000),NA())

Glad to know you got it sorted!

 

[John]

smartin,

I have another question you may be able to help with.

I am trying to get the result from this formula to round,
=IF(Q:Q<=5000,Q:Q*0.06+Q:Q*I:I,Q:Q*0.06+5000*I:I)
No matter how I insert round I get an error??

Thanks for the tip on"N/A" I was trying to get a cell to have "N/A" as a
result just the other day.

 

[Dave Peterson]

Check your other post.

smartin,

I have another question you may be able to help with.

I am trying to get the result from this formula to round,
=IF(Q:Q<=5000,Q:Q*0.06+Q:Q*I:I,Q:Q*0.06+5000*I:I)
No matter how I insert round I get an error??

Thanks for the tip on"N/A" I was trying to get a cell to have "N/A" as a
result just the other day.

 

[smartin]

smartin,

I have another question you may be able to help with.

I am trying to get the result from this formula to round,
=IF(Q:Q<=5000,Q:Q*0.06+Q:Q*I:I,Q:Q*0.06+5000*I:I)
No matter how I insert round I get an error??

Thanks for the tip on"N/A" I was trying to get a cell to have "N/A" as a
result just the other day.

You're welcome!

For rounding, just wrap the whole IF, thus:

=ROUND(IF(Q:Q<=5000,Q:Q*0.06+Q:Q*I:I,Q:Q*0.06+5000*I:I),0)

 

[John]

You're welcome!

For rounding, just wrap the whole IF, thus:

=ROUND(IF(Q:Q<=5000,Q:Q*0.06+Q:Q*I:I,Q:Q*0.06+5000*I:I),0)

A Chap named Dave got me sorted out already on the "round" thing.
But , Thanks again anyway, do appreciate the help.