|
Formula help
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. |
Formula help
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 |
Formula help
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 |
Formula help
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. |
Formula help
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 |
Formula help
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. |
Formula help
.... 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. |
Formula help
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. |
Formula help
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 |
Formula help
.... 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. |
Formula help
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. |
Formula help
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. |
Formula help
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. |
Formula help
"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. |
Formula help
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. |
Formula help
... 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. |
| All times are GMT +1. The time now is 04:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com