I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.

I assume you need more than 7 if statements? How about creating a table
and using Vlookup?


--
Chris Berry
------------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165
View this thread: http://www.excelforum.com/showthread...hreadid=574277

Actually 7 statements would cover most applications... I have never used
vlookup so i would need help with that

"Chris Berry" wrote:


I assume you need more than 7 if statements? How about creating a table
and using Vlookup?


--
Chris Berry
------------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165
View this thread: http://www.excelforum.com/showthread...hreadid=574277

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.

Assuming F28 is always positive....

=MAX(100,CEILING(F28,25))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574277

That works except... the values are in increments of 25 from 100 to 250, then
the increments are 50 from 250 to 500, then in increments of 100 from 500 to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.

.... but you didn't explain this ... mind reading comes extra!

Try:

=IF(F28<=250,MAX(100,CEILING(F28,25)),IF(A1<=500,C EILING(F28,50),CEILING(F28,100)))

HTH

"Florida Richard" wrote:

That works except... the values are in increments of 25 from 100 to 250, then
the increments are 50 from 250 to 500, then in increments of 100 from 500 to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.

My next question is now i must assign that value in k28 a specific wire size
in h31 ie: if k28 is 100 then h31 is "4" if k28 is 125 then h31 is "2" and
that goes on from 100 to 250 in incriments of 25 and the 250, 300, 350, and
400. I am not to concerned above that value.

"Toppers" wrote:

... but you didn't explain this ... mind reading comes extra!

Try:

=IF(F28<=250,MAX(100,CEILING(F28,25)),IF(A1<=500,C EILING(F28,50),CEILING(F28,100)))

HTH

"Florida Richard" wrote:

That works except... the values are in increments of 25 from 100 to 250, then
the increments are 50 from 250 to 500, then in increments of 100 from 500 to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.

Attached is an example of a vlookup table with your data. But with that
large of a table I like the formula better.

I only populated 5000 lines of the table cause of the size limitation.

You could use the formula to arrive at column K and then use a much
smaller lookup table to populate H.


+-------------------------------------------------------------------+
|Filename: Vlookup.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5258 |
+-------------------------------------------------------------------+

--
Chris Berry
------------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165
View this thread: http://www.excelforum.com/showthread...hreadid=574277

.... what do you mean by that goes on ....

K28 H31
100 4
125 2
150 2 (?)
175 ?
200 ?
225 ?
250 ?
300 ?
350 ?

=if(K28<=100,4,if(K28<=250,2,"")??????

"Florida Richard" wrote:

My next question is now i must assign that value in k28 a specific wire size
in h31 ie: if k28 is 100 then h31 is "4" if k28 is 125 then h31 is "2" and
that goes on from 100 to 250 in incriments of 25 and the 250, 300, 350, and
400. I am not to concerned above that value.

"Toppers" wrote:

... but you didn't explain this ... mind reading comes extra!

Try:

=IF(F28<=250,MAX(100,CEILING(F28,25)),IF(A1<=500,C EILING(F28,50),CEILING(F28,100)))

HTH

"Florida Richard" wrote:

That works except... the values are in increments of 25 from 100 to 250, then
the increments are 50 from 250 to 500, then in increments of 100 from 500 to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.

Would

=CEILING(F28,LOOKUP(F28,{100,250,500},{25,50,100}) )

Do what you are looking for?

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Florida Richard" wrote in
message ...
That works except... the values are in increments of 25 from 100 to 250,
then
the increments are 50 from 250 to 500, then in increments of 100 from 500
to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked
through the
entire sheet and it is flawless except for one point. I am attempting
to make
the result for F28 to result a predertimened number in cell K28. The
idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125
then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is
between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and
have
had no sucess. If any one has any ideas please share them.

The series is:

k28 h31
100 4
125 2
150 1
175 1/0
200 2/0
225 3/0
250 4/0
300 250
350 350
400 400


"Toppers" wrote:

... what do you mean by that goes on ....

K28 H31
100 4
125 2
150 2 (?)
175 ?
200 ?
225 ?
250 ?
300 ?
350 ?

=if(K28<=100,4,if(K28<=250,2,"")??????

"Florida Richard" wrote:

My next question is now i must assign that value in k28 a specific wire size
in h31 ie: if k28 is 100 then h31 is "4" if k28 is 125 then h31 is "2" and
that goes on from 100 to 250 in incriments of 25 and the 250, 300, 350, and
400. I am not to concerned above that value.

"Toppers" wrote:

... but you didn't explain this ... mind reading comes extra!

Try:

=IF(F28<=250,MAX(100,CEILING(F28,25)),IF(A1<=500,C EILING(F28,50),CEILING(F28,100)))

HTH

"Florida Richard" wrote:

That works except... the values are in increments of 25 from 100 to 250, then
the increments are 50 from 250 to 500, then in increments of 100 from 500 to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.

Sandy,

.... try it with 99 ... you will #N/A error so perhaps ...

=IF(F28<=100,100,CEILING(F28,LOOKUP(F28,{100,250,5 00},{25,50,100})))


"Sandy Mann" wrote:

Would

=CEILING(F28,LOOKUP(F28,{100,250,500},{25,50,100}) )

Do what you are looking for?

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Florida Richard" wrote in
message ...
That works except... the values are in increments of 25 from 100 to 250,
then
the increments are 50 from 250 to 500, then in increments of 100 from 500
to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked
through the
entire sheet and it is flawless except for one point. I am attempting
to make
the result for F28 to result a predertimened number in cell K28. The
idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125
then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is
between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and
have
had no sucess. If any one has any ideas please share them.

Try:


=LOOKUP(K28,{0,101,126,151,176,201,226,251,301,351 },{"4","2","1","1/0","2/0","3/0","4/0","250","350","400"})

I was (am) confused by "1/0" so i assumed these would result in text fields;
hence everything in quotes in above.

HTH

"Florida Richard" wrote:

The series is:

k28 h31
100 4
125 2
150 1
175 1/0
200 2/0
225 3/0
250 4/0
300 250
350 350
400 400


"Toppers" wrote:

... what do you mean by that goes on ....

K28 H31
100 4
125 2
150 2 (?)
175 ?
200 ?
225 ?
250 ?
300 ?
350 ?

=if(K28<=100,4,if(K28<=250,2,"")??????

"Florida Richard" wrote:

My next question is now i must assign that value in k28 a specific wire size
in h31 ie: if k28 is 100 then h31 is "4" if k28 is 125 then h31 is "2" and
that goes on from 100 to 250 in incriments of 25 and the 250, 300, 350, and
400. I am not to concerned above that value.

"Toppers" wrote:

... but you didn't explain this ... mind reading comes extra!

Try:

=IF(F28<=250,MAX(100,CEILING(F28,25)),IF(A1<=500,C EILING(F28,50),CEILING(F28,100)))

HTH

"Florida Richard" wrote:

That works except... the values are in increments of 25 from 100 to 250, then
the increments are 50 from 250 to 500, then in increments of 100 from 500 to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.

"Toppers" wrote in message
...
Sandy,

... try it with 99 ... you will #N/A error so perhaps ...

Yes I know. I was going by the OP's
That works except... the values are in increments of 25 from 100 to
250,

If there is a chance of F28 being under 100 then I would amend it to:

=CEILING(F28,LOOKUP(F28,{0,100,250,500},{100,25,50 ,100}))

Assuming the 100 was the required return, but the OP does not say what he
wanted.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Toppers" wrote in message
...
Sandy,

... try it with 99 ... you will #N/A error so perhaps ...

=IF(F28<=100,100,CEILING(F28,LOOKUP(F28,{100,250,5 00},{25,50,100})))


"Sandy Mann" wrote:

Would

=CEILING(F28,LOOKUP(F28,{100,250,500},{25,50,100}) )

Do what you are looking for?

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Florida Richard" wrote in
message ...
That works except... the values are in increments of 25 from 100 to
250,
then
the increments are 50 from 250 to 500, then in increments of 100 from
500
to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked
through the
entire sheet and it is flawless except for one point. I am
attempting
to make
the result for F28 to result a predertimened number in cell K28. The
idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and
125
then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is
between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task
and
have
had no sucess. If any one has any ideas please share them.

Excell is not recognizing this as an equation. It just tries to put that
string in the cell

"Toppers" wrote:

Try:


=LOOKUP(K28,{0,101,126,151,176,201,226,251,301,351 },{"4","2","1","1/0","2/0","3/0","4/0","250","350","400"})

I was (am) confused by "1/0" so i assumed these would result in text fields;
hence everything in quotes in above.

HTH

"Florida Richard" wrote:

The series is:

k28 h31
100 4
125 2
150 1
175 1/0
200 2/0
225 3/0
250 4/0
300 250
350 350
400 400


"Toppers" wrote:

... what do you mean by that goes on ....

K28 H31
100 4
125 2
150 2 (?)
175 ?
200 ?
225 ?
250 ?
300 ?
350 ?

=if(K28<=100,4,if(K28<=250,2,"")??????

"Florida Richard" wrote:

My next question is now i must assign that value in k28 a specific wire size
in h31 ie: if k28 is 100 then h31 is "4" if k28 is 125 then h31 is "2" and
that goes on from 100 to 250 in incriments of 25 and the 250, 300, 350, and
400. I am not to concerned above that value.

"Toppers" wrote:

... but you didn't explain this ... mind reading comes extra!

Try:

=IF(F28<=250,MAX(100,CEILING(F28,25)),IF(A1<=500,C EILING(F28,50),CEILING(F28,100)))

HTH

"Florida Richard" wrote:

That works except... the values are in increments of 25 from 100 to 250, then
the increments are 50 from 250 to 500, then in increments of 100 from 500 to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.

... I suspect cell is formatted as TEXT. Reformat as General and re-enter.

"Florida Richard" wrote:

Excell is not recognizing this as an equation. It just tries to put that
string in the cell

"Toppers" wrote:

Try:


=LOOKUP(K28,{0,101,126,151,176,201,226,251,301,351 },{"4","2","1","1/0","2/0","3/0","4/0","250","350","400"})

I was (am) confused by "1/0" so i assumed these would result in text fields;
hence everything in quotes in above.

HTH

"Florida Richard" wrote:

The series is:

k28 h31
100 4
125 2
150 1
175 1/0
200 2/0
225 3/0
250 4/0
300 250
350 350
400 400


"Toppers" wrote:

... what do you mean by that goes on ....

K28 H31
100 4
125 2
150 2 (?)
175 ?
200 ?
225 ?
250 ?
300 ?
350 ?

=if(K28<=100,4,if(K28<=250,2,"")??????

"Florida Richard" wrote:

My next question is now i must assign that value in k28 a specific wire size
in h31 ie: if k28 is 100 then h31 is "4" if k28 is 125 then h31 is "2" and
that goes on from 100 to 250 in incriments of 25 and the 250, 300, 350, and
400. I am not to concerned above that value.

"Toppers" wrote:

... but you didn't explain this ... mind reading comes extra!

Try:

=IF(F28<=250,MAX(100,CEILING(F28,25)),IF(A1<=500,C EILING(F28,50),CEILING(F28,100)))

HTH

"Florida Richard" wrote:

That works except... the values are in increments of 25 from 100 to 250, then
the increments are 50 from 250 to 500, then in increments of 100 from 500 to
6000.

"Toppers" wrote:

Try:

=IF(F28<=100,100,(INT((F28-1)/25)+1)*25)

HTH

"Florida Richard" wrote:

I am constructing an electrical calculation sheet. I have worked through the
entire sheet and it is flawless except for one point. I am attempting to make
the result for F28 to result a predertimened number in cell K28. The idea is
as follows:

If f28 is less than 100 then k28 is 100, if f28 is between 101 and 125 then
k28 is 125, if f28 is between 126 and 150 then k28 is 150, if f28 is between
151 and 175 then k28 is 175, and so on and so on...

I have tried about 20 different combinations to complete this task and have
had no sucess. If any one has any ideas please share them.