Formula help

F

Florida Richard

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

Chris Berry

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

Florida Richard

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

Florida Richard

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

Toppers

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

Try:

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

HTH
 
F

Florida Richard

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

Chris Berry

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 |
+-------------------------------------------------------------------+
 
T

Toppers

.... 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,"")??????
 
F

Florida Richard

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
 
T

Toppers

Sandy,

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

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

Toppers

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
 
S

Sandy Mann

Toppers said:
Sandy,

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

Yes I know. I was going by the OP's
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

[email protected]
[email protected] with @tiscali.co.uk
 
F

Florida Richard

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