| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional minimum formula
im looking for a formula where a minimum will be returned, if the cells
aren't 0 or blank. for example: Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = 24" so, we have 2 spans, with anti-buckling clips at 24" on centre. I would like the cell containing the formula to equal 24. Lets now say we don't have any anti-buckling clips, and the cell is left blank. Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = The cell should read: 80 Any ideas? im trying to work with a nested if statement inside a =min() function, but im not sure how to return a 'nothing here, move on' line. Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional minimum formula
Please tell (or show) us what is in a few sample cells - also tell us what
cells are being used for these. All the references to spans and spacing is meaningless to us. =MIN(IF(A1:A10<0,A1:A10)) will return the non-zero minimum of the range A1:A10 It is an array formula so must be committed with CTRL+SHIFT+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... im looking for a formula where a minimum will be returned, if the cells aren't 0 or blank. for example: Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = 24" so, we have 2 spans, with anti-buckling clips at 24" on centre. I would like the cell containing the formula to equal 24. Lets now say we don't have any anti-buckling clips, and the cell is left blank. Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = The cell should read: 80 Any ideas? im trying to work with a nested if statement inside a =min() function, but im not sure how to return a 'nothing here, move on' line. Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional minimum formula
With spans in cells A1:A3 and Anti-buckling clip spacing in B1; try the below
formula =SMALL((A1:A3,B1),COUNTIF(A1:A3,0)+1) If this post helps click Yes --------------- Jacob Skaria "Derrick" wrote: im looking for a formula where a minimum will be returned, if the cells aren't 0 or blank. for example: Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = 24" so, we have 2 spans, with anti-buckling clips at 24" on centre. I would like the cell containing the formula to equal 24. Lets now say we don't have any anti-buckling clips, and the cell is left blank. Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = The cell should read: 80 Any ideas? im trying to work with a nested if statement inside a =min() function, but im not sure how to return a 'nothing here, move on' line. Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional minimum formula
ok sorry. i was hoping spans and stuff were helping . .the column titles etc.
A2:A4 (spans) = 120",80",0" respectively B2 (clip spacing) = 24" i want C2 (Min length) = 24. if B2 = "" (Left blank because of no clips) i want C2= 80 Also, im not a fan of using arrays... i can, but i have problems getting them to work does this help you? "Bernard Liengme" wrote: Please tell (or show) us what is in a few sample cells - also tell us what cells are being used for these. All the references to spans and spacing is meaningless to us. =MIN(IF(A1:A10<0,A1:A10)) will return the non-zero minimum of the range A1:A10 It is an array formula so must be committed with CTRL+SHIFT+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... im looking for a formula where a minimum will be returned, if the cells aren't 0 or blank. for example: Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = 24" so, we have 2 spans, with anti-buckling clips at 24" on centre. I would like the cell containing the formula to equal 24. Lets now say we don't have any anti-buckling clips, and the cell is left blank. Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = The cell should read: 80 Any ideas? im trying to work with a nested if statement inside a =min() function, but im not sure how to return a 'nothing here, move on' line. Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional minimum formula
=SMALL((A1:A4,B2),COUNTIF(A1:A4,0)+1)
If this post helps click Yes --------------- Jacob Skaria "Derrick" wrote: ok sorry. i was hoping spans and stuff were helping . .the column titles etc. A2:A4 (spans) = 120",80",0" respectively B2 (clip spacing) = 24" i want C2 (Min length) = 24. if B2 = "" (Left blank because of no clips) i want C2= 80 Also, im not a fan of using arrays... i can, but i have problems getting them to work does this help you? "Bernard Liengme" wrote: Please tell (or show) us what is in a few sample cells - also tell us what cells are being used for these. All the references to spans and spacing is meaningless to us. =MIN(IF(A1:A10<0,A1:A10)) will return the non-zero minimum of the range A1:A10 It is an array formula so must be committed with CTRL+SHIFT+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... im looking for a formula where a minimum will be returned, if the cells aren't 0 or blank. for example: Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = 24" so, we have 2 spans, with anti-buckling clips at 24" on centre. I would like the cell containing the formula to equal 24. Lets now say we don't have any anti-buckling clips, and the cell is left blank. Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = The cell should read: 80 Any ideas? im trying to work with a nested if statement inside a =min() function, but im not sure how to return a 'nothing here, move on' line. Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional minimum formula
can you explain how this formula works?
Small(Array,K) countif(range, criteria) so minimum non-zero value = Small(Array,K =countif(Range, criteria)) 1. I get the (A1:A3,B1). 2. doesn't countif return the number of cells which meet the criteria? so if A1:A3 cells are non-0, it returns 3, then plus 1 - so 4 3. then, small looks for the 4th smallest - the largest one? 4. why not include B1 in the countif? am i right or am i making a mistake in my logic? "Jacob Skaria" wrote: With spans in cells A1:A3 and Anti-buckling clip spacing in B1; try the below formula =SMALL((A1:A3,B1),COUNTIF(A1:A3,0)+1) If this post helps click Yes --------------- Jacob Skaria "Derrick" wrote: im looking for a formula where a minimum will be returned, if the cells aren't 0 or blank. for example: Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = 24" so, we have 2 spans, with anti-buckling clips at 24" on centre. I would like the cell containing the formula to equal 24. Lets now say we don't have any anti-buckling clips, and the cell is left blank. Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = The cell should read: 80 Any ideas? im trying to work with a nested if statement inside a =min() function, but im not sure how to return a 'nothing here, move on' line. Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional minimum formula
jacob, this formula will does not include B2.
I put it in, and if it's smaller, it still isn't being used. i tried =SMALL((A1:A4,B2),COUNTIF((A1:A4,B2),0)+1) and that returns a =#Value error "Jacob Skaria" wrote: =SMALL((A1:A4,B2),COUNTIF(A1:A4,0)+1) If this post helps click Yes --------------- Jacob Skaria "Derrick" wrote: ok sorry. i was hoping spans and stuff were helping . .the column titles etc. A2:A4 (spans) = 120",80",0" respectively B2 (clip spacing) = 24" i want C2 (Min length) = 24. if B2 = "" (Left blank because of no clips) i want C2= 80 Also, im not a fan of using arrays... i can, but i have problems getting them to work does this help you? "Bernard Liengme" wrote: Please tell (or show) us what is in a few sample cells - also tell us what cells are being used for these. All the references to spans and spacing is meaningless to us. =MIN(IF(A1:A10<0,A1:A10)) will return the non-zero minimum of the range A1:A10 It is an array formula so must be committed with CTRL+SHIFT+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... im looking for a formula where a minimum will be returned, if the cells aren't 0 or blank. for example: Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = 24" so, we have 2 spans, with anti-buckling clips at 24" on centre. I would like the cell containing the formula to equal 24. Lets now say we don't have any anti-buckling clips, and the cell is left blank. Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = The cell should read: 80 Any ideas? im trying to work with a nested if statement inside a =min() function, but im not sure how to return a 'nothing here, move on' line. Thanks! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional minimum formula
Sorry be be a dolt, but I still cannot see what column A has to do with the
problem =IF(B2="",80,MIN(B2:D2)) will give the minimum value of B2:D2 if B2 is not blank Does this help? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... ok sorry. i was hoping spans and stuff were helping . .the column titles etc. A2:A4 (spans) = 120",80",0" respectively B2 (clip spacing) = 24" i want C2 (Min length) = 24. if B2 = "" (Left blank because of no clips) i want C2= 80 Also, im not a fan of using arrays... i can, but i have problems getting them to work does this help you? "Bernard Liengme" wrote: Please tell (or show) us what is in a few sample cells - also tell us what cells are being used for these. All the references to spans and spacing is meaningless to us. =MIN(IF(A1:A10<0,A1:A10)) will return the non-zero minimum of the range A1:A10 It is an array formula so must be committed with CTRL+SHIFT+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... im looking for a formula where a minimum will be returned, if the cells aren't 0 or blank. for example: Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = 24" so, we have 2 spans, with anti-buckling clips at 24" on centre. I would like the cell containing the formula to equal 24. Lets now say we don't have any anti-buckling clips, and the cell is left blank. Spans# 1 - 120", #2 - 80", #3 - 0" Anti-buckling clip spacing = The cell should read: 80 Any ideas? im trying to work with a nested if statement inside a =min() function, but im not sure how to return a 'nothing here, move on' line. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Minimum formula | Excel Worksheet Functions | |||
| MINIMUM - CONDITIONAL | Excel Discussion (Misc queries) | |||
| Looking for a conditional minimum value | Excel Discussion (Misc queries) | |||
| conditional minimum value of various cells? | Excel Worksheet Functions | |||
| Conditional format of minimum number | Excel Worksheet Functions | |||
Linear Mode
