I have a data set that has Material (Col A) Plant (Col B) Storage Loc (col C), and Safety Stock (col D). The safety stock will be the same for every row with the same material plant combo even thought the Storage loc may change. I need to look up the total safety stock for each material. So i need a formula that will do a Sum if of the material but with a function inside it to average or find the unique values only at the material/plant level.
Posts by goin4boge
-
-
Re: Flag if data is in top 80% of range
easy after several hours of thinking on it 8-)
created a column with % of total
Created a column that sumif the % is greater than that lines data
created a column that said if this value was < 80% then "In Top 80%", else, "NOT in Top 80%" -
Re: Flag if data is in top 80% of range
I tried to change it to =if(c1>=max(if(A:A=A1,C:C)*.8,"Top 80%","") but that did not work. This formula checks to see if the value of the single data point is >= 80% of the max value. What I need is to identify all the parts that make up the top 80%. Does that make sense?
-
Re: Flag if data is in top 80% of range
looks like maxif(s) was added for 2016. I have 2013. any ideas for excel 2013?
-
Re: Flag if data is in top 80% of range
Excel is not liking the maxifs function, is that a valid function?
-
I need a formula that will flag a cell if it is in the top 80% of the range total value. Column A = Salesman Column B = Material Column C = Revenue Column D = Top 80% flag?. Need it to see if the material is in the top 80% for each Salesman's total sales.
-
-
-
Re: disable save/save as not working?
So my goal on this is to only allow the user to save after all info has been entered. How would I now go in and have the VBA save the file?
-
Re: disable save/save as not working?
ah...it's the easy stuff huh?
Thanks that got it
-
Re: disable save/save as not working?
-
Re: disable save/save as not working?
still nothing
-
I am trying to disable save/save as in my file, but it is not working, any ideas?
-
Re: Accrual PTO
Not exactly sure if this is what you are trying to do, try putting this formula in
=IF((TODAY()-C2)/365<5,MIN(WEEKNUM(TODAY())/2*0.69,18),IF(AND((TODAY()-C2)/365>5,(TODAY()-C2)/365<10),MIN(WEEKNUM(TODAY())/2*0.77,20),MIN(WEEKNUM(TODAY())/2*0.88,23)))
-
-
-
Re: Delete rows from one work sheet and paste into another if conditions are met
Try this:
Code
Display MoreSub main() Dim rowc, cust, rowo As String rowc = 2 Do Until Cells(rowc, 2) = "" If Cells(rowc, 2) = 0 Then Rows(rowc).Select Selection.Cut cust = Cells(rowc, 1) Worksheets(cust).Select rowo = 2 Do Until Worksheets(cust).Cells(rowo, 2) = "" If Cells(rowo, 2) = "" Then Else rowo = rowo + 1 End If Loop Worksheets(cust).Rows(rowo).Select Selection.Insert Shift:=xlDown rowo = 2 Worksheets("Open PO's Before").Select Rows(rowc).Select Selection.Delete Shift:=xlUp Else rowc = rowc + 1 End If Loop End Sub
-
I have a worksheet that has 4 pivot tables. These tables are all linked to the same file stored on a network drive. It is a Usage file and that file changes every week......(in week 1 file would be UsageWk1.xslx, in week 2 file would be UsageWk2.xslx......). I would like to link these 4 pivot tables so that i can update the external data sourece 1 time for all 4 pivot tables. Any way to do that?
-
Re: Formulas starting with "=if(" become hyperlinks "mailto:=@if("
i have had a similar thing happen to me in the past, my IT group had to "reset my creditials", not sure what all that intails, but as it did, the hyperlinks stop showing up. I then had to go in and fix all the current hyper links. type a 1 in a blank cell. COpy that cell. select all the cells with the hyperlinks, past-special-multiply. this will remove the hyperlinks....and the formatting. hope that helps
-
Re: Sumproduct with Max
yeah, i tried posting it a few different ways with no help....got a hit this time
I have applied this logic to a more complicated formula and got it to work when i type it in excel. It ends up being a CSE formula when i add all the other functions to this formula. I am trying to have VBA input the CSE formula in but it enters it as a regular formula. How do i get it to put it as a CSE?
CodeRange("P" & drow).Select Selection.FormulaArray = _ "=IF(IFERROR(MODE(IF(R6C10:R" & lrow & "=RC[-6],R6C15:R" & lrow & "C15)),"""")="""",SUMPRODUCT(MAX((R6C10:R" & lrow & "=RC[-6])*(R6C1:R" & lrow & "C1<>""CAN"")*R6C15:R" & lrow & "C15)),IFERROR(MODE(IF(R6C10:R" & lrow & "C10=RC[-6],R6C15:R" & lrow & "C15)),""""))"