# Posts by pangolin

• ## Count Unique Records Meeting 2 Criteria with 2 possible values for Condition 1

Re: Count Unique Records Meeting 2 Criteria with 2 possible values for Condition 1

for getting unique values use this formula

=SUM(IF(FREQUENCY(IF((\$A\$2:\$A\$14)<>"",IF(((\$B\$2:\$B\$14)=\$B\$18),IF((((\$C\$2:\$C\$14)=\$A\$20)+((\$C\$2:\$C\$14)=\$A\$21)),MATCH(\$A\$2:\$A\$14,\$A\$2:\$A\$14,0)))),ROW(\$A\$2:\$A\$14)-ROW(\$A\$2)+1),1))

note this is an array formula and needs to be confirmed by Ctrl+Shift+Enter

• ## Count Unique Records Meeting 2 Criteria with 2 possible values for Condition 1

Re: Count Unique Records Meeting 2 Criteria with 2 possible values for Condition 1

sorry posted wrongly....

• ## tiered pricing

Re: tiered pricing

why not use a simple IF formula like

=IF(A1<=1,750000,750000+(A1-1)*300000)

• ## Dynamic Chart for last 5 rows in Table

Re: Dynamic Chart for last 5 rows in Table

if there are non-numeric values appearing in the range then use this formula

=SUM(IF(ISNUMBER(A:A)=TRUE,(A:A),0)*IF((ROW(A:A))>(MAX(((A:A)<>"")*(ROW(A:A)))-5),1,0))

note this is an array formula and needs to be confirmed by Ctrl+Shift+Enter

you are getting zero probably because the formula is not getting confirmed as above...or else post an example workbook in the forum for further assistance

• ## Dynamic Chart for last 5 rows in Table

Re: Dynamic Chart for last 5 rows in Table

this is one approach

this formula assumes no internediate blank rows and all numeric values

=SUM((A:A)*IF((ROW(A:A))>(MAX(((A:A)<>"")*(ROW(A:A)))-5),1,0))/5

change range references to suit

as usual it is an array formula and needs to be conformed by Ctrl+Shift+Enter

• ## Data Analysis Add in

probably REGRESSION in the form Y = a + bX

so you input a series of X and series of Y then compute the regression equation

then data analysis can will give the value oif a and b which can be used to predict the value of Y for a given value of X

note LINEST function will generate the same values for the regression while INTERCEPT can be used for 'a' while slope can be used for 'b'

• ## Extract text or integers from between parentheses

Re: Extract text or integers from between parentheses

this is a formula based approach JIC someone needs it

=IF(LEN(A\$1)-LEN(SUBSTITUTE(A\$1,"(",""))<ROWS(\$A\$1:\$A1),"",MID(A\$1,FIND("~",SUBSTITUTE(A\$1,"(","~",ROWS(\$A\$1:\$A1)))+1,FIND("~",SUBSTITUTE(A\$1,")","~",ROWS(\$A\$1:\$A1)))-FIND("~",SUBSTITUTE(A\$1,"(","~",ROWS(\$A\$1:\$A1)))-1))

• ## Pareto Chart based on Pivot Table

Re: Pareto Chart based on Pivot Table

not understood your post but is this what ur looking for

• ## Weighted Average with uneven splits

Re: Weighted Average with uneven splits

this is one approach

put this formula in cell D1

note this is an array formula and needs to be conformed by Ctrl+Shift+Enter

after copying into cell D1 and conforming as above you can copy it down to the other cells

=INDEX(\$B\$5:\$B\$8,SUM(IF(ISNUMBER(\$B\$15:B15)=TRUE,1,0)))*C15/IFERROR(SUM((\$C\$15:\$C\$26)*IF(ROW(\$B\$15:\$B\$26)>=LARGE(ROW(\$B\$15:\$B\$26)*(ISNUMBER(\$B\$15:\$B\$26)=TRUE),SUM(IF(ISNUMBER(\$B\$15:\$B\$26)=TRUE,1,0))-(SUM(IF(ISNUMBER(\$B\$15:B15)=TRUE,1,0))-1)),1,0)*IF(ROW(\$B\$15:\$B\$26)<LARGE(ROW(\$B\$15:\$B\$26)*(ISNUMBER(\$B\$15:\$B\$26)=TRUE),SUM(IF(ISNUMBER(\$B\$15:\$B\$26)=TRUE,1,0))-SUM(IF(ISNUMBER(\$B\$15:B15)=TRUE,1,0))),1,0)),SUM((\$C\$15:\$C\$26)*IF(ROW(\$B\$15:\$B\$26)>=LARGE(ROW(\$B\$15:\$B\$26)*(ISNUMBER(\$B\$15:\$B\$26)=TRUE),1),1,0)))

• ## Sum First Occurrence Based Upon Multiple Criteria

Re: Sum First Occurrence Based Upon Multiple Criteria

one apprach could be to create a helper column that will idientify the second appearance of a material with Stock in Col D

for eg create a helper column put this in row 2 and copy down

=IF(D2=\$D\$6,SUMPRODUCT(((\$A\$2:A2)=A2)*((\$D\$2:D2)=\$D\$6)),0)

then you can calculate the stock using a simple formula

=SUMIFS(F2:F21,\$D\$2:\$D\$21,\$D\$6,\$I\$2:\$I\$21,1)

where column I2:I21 is the helper column...you can then copy this formula across for the various months

IMHO it will be difficult to create functionality of helper column within one cell

hope this helps

• ## Sum of multiple criteria in a column

Re: Sum of multiple criteria in a column

use the function SUMIFS.....see the help file for further info and syntax

• ## Return a specific value of the cell after clicking Checkbox !

Re: Return a specific value of the cell after clicking Checkbox !

see this code

Code
``````Private Sub CheckBox1_Click()
Sheets("sheet1").Range("I2").Value = Sheets("sheet1").Range("G2").Value
End Sub``````
• ## Make the range of an Array dynamic based on specified date

Re: Make the range of an Array dynamic based on specified date

use this formula

=(PRODUCT(1+B5:INDEX(B5:M5,MATCH(\$B\$2,\$B\$4:\$M\$4,0))%)-1)*100

note this is an array formula and requires to be confirmed by Ctrl+Shift+Enter

• ## Dynamic Chart for last 5 rows in Table

Re: Dynamic Chart for last 5 rows in Table

this is one approach

=INDEX(A:A,SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A))))-5+ROWS(\$A\$1:A1))

put it in lets say Cell B1 and copy down next four rows

this references entire column A...change range references to suit requirements

note this formula assumes that there are no intermediate blank rows within the 4 rows above the last occupied row in the column

if you have any internediate blank rows within the 4 coccupied rows above the last occupied row in the column then you can use this formula

=INDEX(A:A,LARGE((A:A<>"")*(ROW(A:A))*((ISBLANK(A:A))=FALSE),ROWS(\$A\$1:A1)))

this is an array formula and needs to be confirmed by Ctrl+Shift+Enter

• ## Group same dates and Sum up another column

Re: Group same dates and Sum up another column

see if this code helps

• ## retain leading zeros in calculated cells

Re: retain leading zeros in calculated cells

from what I have understood I can offer two options

1. You can use TEXT in formulas by couching the formulas using VALUE function for eg =SUM(VALUE(TEXT)))

2. If you want leading zeros to appear in charts then once you have constructed the chart then right click the series and go to >> Format Data Labels >> Number >> Custom and there you can set your custom format like 0000 which will show the series in the chart with leading zeros

hope that helps

• ## VBA to populate 2nd and 3rd listboxes based on 1st listbox data.

Re: VBA to populate 2nd and 3rd listboxes based on 1st listbox data.

what you will have to do is create name range for each of the columns in database

for eg in the code below the names are in range myname
id in myid and dept in mydept and so on

then for Listbox1 for Rowsource property refer to myname

then put this code in the private sub Listbox1_change section

Code
``````Private Sub ListBox1_Change()
If ListBox2.ListCount = 1 Then
ListBox2.RemoveItem 0
End If
If ListBox2.ListCount = 0 Then
End If
End Sub``````

what will happen is upon launch of user form the Listbox1 will get populated with the various names

when you select any name the above code will get triggered and the other two list boxes will get poplulated with the relevant values

make sure that Rowsource property of the other two List boxes is kept blank

hope that helps

• ## Summarize the Raw Materials for finished products from a production calendar

Re: Summarize the Raw Materials for finished products from a production calendar

one approach can be to organise the raw data of Production Details into a matrix and also to organise the Raw Material Breakdown data correctly

then use matrix multiplication to generate the RM Consumption summary

this will eliminate all the extra sheets (although you will have to create one helper matrix)

see the attached file...the orgnaised data / matrix are highlighted....I have deleted the not reqd sheets to emphasise the point

hope that helps

• ## Separate Numbers From Within a Single Cell01

Re: Separate Numbers From Within a Single Cell01

Quote from Ratiocination;725244

Sorry my mistake the number format is infact
Category - Custom
Type - hh:mm

Is this still possible to do?

if that is the case then using function HOUR and MINUTE one can extract the hh:mm values respectively

• ## Clear Column content based on Column heading

Re: Clear Column content based on Column heading