Home |
Search |
Today's Posts |
#1
|
|||
|
|||
increments
I have values in cells f5 and h5.These values range from 1.01 to 1000,but are in bands, and there only allowed increments in each band. Min max increment 1.00 2.00 0.01 2.02 3.00 0.02 3.05 4.00 0.05 4.10 6.00 0.10 6.20 10.00 0.20 10.50 20.00 0.50 21.00 30.00 1.00 32.00 50.00 2.00 55.00 100.00 5.00 110.00 1000.00 10.00 Thus allowed values are 1.01,1.02,-----,2.02,2.04-----,3.05,3.10,3.15 etc. In cell n5 I want create a condition that will carry out an action if 1.the difference between the values in h5 and f5 is greater than two or more increments and 2.f5 has A value less than 10. This action is to place in cell o5 a formula that puts in this cell A value 1 increment less than the current value in h5. To try and make myself clearer,say the values in f5 and h5 are 4.2 and 17 respectively. F5 is less than 10.There is a difference of more than or equal to 2 increments. The two conditions are fulfilled thus I place 16.5( one increment less than 17) in cell o5. -- pytelium ------------------------------------------------------------------------ pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521 View this thread: http://www.excelforum.com/showthread...hreadid=474593 |
#2
|
|||
|
|||
In your example, 4.2 and 17 are *not* in the same band!
So is the increment to *always* be determined by the contents of H5? OR Will the possibility exist that the larger number *may* be in F5, In which case, the increment is determined by the contents of F5? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "pytelium" wrote in message ... I have values in cells f5 and h5.These values range from 1.01 to 1000,but are in bands, and there only allowed increments in each band. Min max increment 1.00 2.00 0.01 2.02 3.00 0.02 3.05 4.00 0.05 4.10 6.00 0.10 6.20 10.00 0.20 10.50 20.00 0.50 21.00 30.00 1.00 32.00 50.00 2.00 55.00 100.00 5.00 110.00 1000.00 10.00 Thus allowed values are 1.01,1.02,-----,2.02,2.04-----,3.05,3.10,3.15 etc. In cell n5 I want create a condition that will carry out an action if 1.the difference between the values in h5 and f5 is greater than two or more increments and 2.f5 has A value less than 10. This action is to place in cell o5 a formula that puts in this cell A value 1 increment less than the current value in h5. To try and make myself clearer,say the values in f5 and h5 are 4.2 and 17 respectively. F5 is less than 10.There is a difference of more than or equal to 2 increments. The two conditions are fulfilled thus I place 16.5( one increment less than 17) in cell o5. -- pytelium ------------------------------------------------------------------------ pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521 View this thread: http://www.excelforum.com/showthread...hreadid=474593 |
#3
|
|||
|
|||
The larger number will have always be in h5 except in the case where h5 is blank -- pytelium ------------------------------------------------------------------------ pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521 View this thread: http://www.excelforum.com/showthread...hreadid=474593 |
#4
|
|||
|
|||
I don't really understand what the significance of a "condition in N5"
means, BUT ... Try this out. Create a datalist, in say Y1 to Z19 Y Z 1 0.01 2 0.01 2.01 0.02 3 0.02 3.01 0.05 4 0.05 4.01 0.1 6 0.1 6.01 0.2 10 0.2 10.01 0.5 20 0.5 20.01 1 30 1 30.01 2 50 2 50.01 5 100 5 100.01 10 Then, enter this formula in O5: =IF(AND(F5<10,H5-F5=(LOOKUP(H5,Y1:Z19))*2),H5-LOOKUP(H5,Y1:Z19),"") If criteria is not met, O5 will remain blank. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "pytelium" wrote in message ... The larger number will have always be in h5 except in the case where h5 is blank -- pytelium ------------------------------------------------------------------------ pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521 View this thread: http://www.excelforum.com/showthread...hreadid=474593 |
#5
|
|||
|
|||
Excellent stuff RD,working perfectly,thank you. -- pytelium ------------------------------------------------------------------------ pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521 View this thread: http://www.excelforum.com/showthread...hreadid=474593 |
#6
|
|||
|
|||
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "pytelium" wrote in message ... Excellent stuff RD,working perfectly,thank you. -- pytelium ------------------------------------------------------------------------ pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521 View this thread: http://www.excelforum.com/showthread...hreadid=474593 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I make multiple room scheduler (Excel) 15 minute increments? | Excel Discussion (Misc queries) | |||
Need formula to add increments of 0.16 seconds to time | Excel Discussion (Misc queries) | |||
ROUNDUP IN 0.2 INCREMENTS | Excel Worksheet Functions | |||
How do I sum a formula and paste it in 7 cell increments? | Excel Discussion (Misc queries) | |||
Field whose value increments automatically | Excel Discussion (Misc queries) |