trying to find formula

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

cells AP6:AP30 has data it could = to 2 to 8 when entered Now if i enter
data in cells AS6:AS30 I AM COUNTING these CELLS in AS31 IT could = to 1 to 4
So what i want to do is to have AS31 Multiplied by the least amount of the
cells AP6:AP30 whitch might be 2 to 8
 
Hi Mike,

Not sure that I have interpretted your question correctly but it appears
that you want to find the minimum value in the range AP6:AP30 and multiply
that by the count of cells containing values in range AS6:AS30. If my
assumption is correct then the following will return the minimum value.

=MIN(AP6:AP30)

If in cell AS31 you are counting cells with values in range AS6:AS30 then I
assume you are using the following

=COUNT(AS6:AS30)

Therefore =COUNT(AS6:AS30)*MIN(AP6:AP30) should return what you want.
 
Hi again Mike,

Need a UDF (User Defined Function) to do this if you want to avoid zeros. As
you have posted in a New User area I'll give you brief instructions on how
to copy the code into your workbook.

Set your Macro Security to medium. (Notify). See Help for how to do this.
(If using xl2007 then close Excel and re-open after doing this.)

Open the required workbook.

Alt/F11 to open the VBA Editor.

Select menu item Insert then click Module.

Copy the code below (between the asterisk lines) and paste it into the white
area of the module.

Close the VBA editor. (Red X top right).

Save the workbook. (If xl2007 then use Save as a Macro enabled workbook.)

Now you can enter the formula on the worksheet as per the following.

=COUNT(AS6:AS30)*smallest(AP6:AP30)

If you need more help to allow macros etc then get back to me but tell me
what version of Excel you are using.


'*************************************
Function Smallest(Target As Range)

Application.Volatile

Dim i As Long

For i = 1 To Target.Rows.Count
If WorksheetFunction.Small(Target, i) > 0 Then
Smallest = WorksheetFunction.Small(Target, i)
Exit For
End If
Next i

End Function
'***********************************
 
Another try Mike,

Change the code to below. Should have been Target.Cells.Count not
Target.Rows.Count.

While it will work in your case, using cells.count will make it more generic
if the cells happen to be in a row across the sheet instead of in a column
down the sheet.

'*************************************
Function Smallest(Target As Range)

Application.Volatile

Dim i As Long

For i = 1 To Target.Cells.Count
If WorksheetFunction.Small(Target, i) > 0 Then
Smallest = WorksheetFunction.Small(Target, i)
Exit For
End If
Next i

End Function
'***********************************
 
Thanks for trying its close but not working i have 2 colums AP6:AP37 WHEN the
DATA IS ENTERED it will read 1 to 8 1 to 4 =1 person over time hours & 4 to
8 = 2 people over time hours now in colum AS6:AS37 I AM COUNTING these cells
when i enter a clock # the clock # will =1 person & 2 clock # will =2 people
so what i am trying to do is figure out the clock # overtime whitch is time &
half or 1.5
 
I don't really understand the problem. Each time you post the problem seems
to change.
 
column AP = LINE that is running production some lines have 2 people & some
have 1 so for instance i MIGHT HAVE AP6 witch might be 2 OR 4 = hours worked
1 person ON THIS LINE so it will = to 2 or 4 hours witch works Then i have
AP13 WITCH MIGHT BE 2 or 4 = hours actually worked but there is 2 people on
this so AP13 reads 4 or 8 pending if they worked 2 hours or 4 hours because
there is 2 people on this line Now in column AS6:AS37 I PUT IN CLOCK# FOR
indirect labour say if 2 clock# entered i want to calculate AP column actual
hours worked times AS column indirect labour at time & half
 
Mike said:
column AP = LINE that is running production some lines have 2 people & some
have 1 so for instance i MIGHT HAVE AP6 witch might be 2 OR 4 = hours worked
1 person ON THIS LINE so it will = to 2 or 4 hours witch works Then i have
AP13 WITCH MIGHT BE 2 or 4 = hours actually worked but there is 2 people on
this so AP13 reads 4 or 8 pending if they worked 2 hours or 4 hours because
there is 2 people on this line Now in column AS6:AS37 I PUT IN CLOCK# FOR
indirect labour say if 2 clock# entered i want to calculate AP column actual
hours worked times AS column indirect labour at time & half I want total hours of indirect hours in cell AS38 Remember AS13 MIGHT READ 4 because of 2 people on & actual overtime hours is 2 so 2 indirect clock # should read 6 overtime hours
Click to expand...
 
Back
Top