Second Largest Value with criteria in excel
I'm trying to find the second largest value of a given criteria in excel. So the formula I was trying to use was
The problem is that it is returning the second largest overall value, but I want the second largest value where column B is 1. Better Answers would be helpful.
You are using an array (CSE) formula and you did not enter it the key combination for array formuas. Edit the cell again and click
Or if you want a normal (non CSE) formula, use this:
See also questions close to this topic
- Excel Pivot Repeat all items
EXCEL: Extracting Milliletres and Litres from cell using vba or formula
I am trying to extract the Milliliters and Liters from text(Only the value, not the ml or Ltr), but as there is no specific standard, it is difficult to do a formula. Below is some examples of the types of descriptions I have:
- Settlement 2.5% (Settlement Discount) - BAGDOL/1 (U-POL DOLPHIN GLAZE Brushable Stopper Bag 440ml) - P38/5 (ISOPON P38 LIGHTWEIGHT FILLER 3Ltr) - EGC21TT (EUROPEAN COATINGS PRIMER 4:1 HS 4Ltr TRIPLE TIGHT) - RLT/1KIT (U-POL RAPTOR TINTABLE 750ml & 250ml STANDARD HARDENER KIT) - CCWP/AL (U-POL CUSTOM CAN Pregassed Aerosol 400ml (Waterbased))
I have used the formula below which I feel I have over-complicated.:
So those with 440ml returns 440 and those that say 3Ltr return 3000 - But this is only if it is at the end of the cells text.
I want to be able to extract the value of the ml or Ltr no matter where it is in the text. Those that have more than one set of ml value in it, I want to sum them. Example
: 750ml & 250mlshould be 1000.
Settlement 2.5% (Settlement Discount)should return 0.
Is there a simple way of doing this ? Id be happier with doing it in VBA, as I think this would be less complicated.
Any help would be appreciated!
Check If File on File name from Cell string exist on Dir
I can't resolve my problem.
- On Column A: I have a file name (without extension) as text.
- On Column B: I have a string for destination folder (just in case)
- On Column C: I want get True or False if File from name (Column A in this row) exist on Local or Remote Dir.
- On Column D: I want get a path to this file.
- On Column E: I want get a hyperlink to file as File name with extension)
- Button (for now, but in next it will be static path to FTP): I use button for choose a Folder with SubFolders to check if File on File Name (from Column A in each row) exist somewhere there.
So, I try make some script on VBA, that I could run from Ribbon in opened Excel.
When I open Excel I have only Columns A:B. Columns C:E are empty. In one Excel File it will be few to even few thousands rows for check. My Excel will looks like that:
Column A - Column B - Column C - Column D - Column E DA-SPK1065 E - 245 - True (exist) - /Users/bart/Temp/Source Folder 1 - LINK DT-SA21WZ5 E - 210 - False (Not found) - (Empty cell) - (Empty cell) LC-5UUUXAC E - 110 - True (exist) - /Users/bart/Temp/Source Folder 2 - LINK AR-SP1C265 E - 210 - True (exist) - /Users/bart/Temp/Source Folder 4 - LINK
I have vba script:
Option Explicit Dim LRow As Integer Dim LPath As String Dim LExtension As String Dim LContinue As Boolean Dim fileName As String Sub CheckIfFileExist() 'Initialize variables LContinue = True LRow = 2 LExtension = ".tif" LPath = MacScript("choose folder as string") If LPath = "" Then MsgBox "Not choose a folder" End if 'Loop through all column A values until a blank cell is found While LContinue 'Found a blank cell, do not continue If Len(Range("A" & CStr(LRow)).Value) = 0 Then LContinue = False Else 'Check if file exists for file name If Len(Dir(LPath, Range("A" & CStr(LRow)).Value)) = 0 Then Range("C" & CStr(LRow)).Value = "Not Exists" Range("C" & CStr(LRow)).Font.Bold = True Else Range("C" & CStr(LRow)).Value = "On Hand" Range("C" & CStr(LRow)).Font.Bold = False End If LRow = LRow + 1 End If Wend End Sub
Problem: Script can't find a file.
R - how to find max date in R using sqldf
Getting all corresponding rows after group by with max date?
I have csv file as
a = read.csv(file = "a.csv", header = TRUE, sep = ",")
b = sqldf("Select psno , Field_description , log_new_value , MAX (inserted_on) from a group by psno having log_new_value = 'Yes'")
But it's giving the wrong answer?
Any experts can help me solving this please? Thanks.
Maximum of a part of a signal
signalcreated like so:
Tinitial =0; Tfinal = 1; NbEch = 300; Tech = (Tfinal - Tinitial)/(NbEch-1); t = Tinitial : Tech: Tfinal; % time vector signal = sin(2*pi*t*3) + 0.25*sin(2*pi*t*50) + randn(size(t))/10; figure plot(t,signal)
I want to calculate the signal maximum between 0% and 10% of the time and between 50% and 60% of the time. I've tried using
ginputon the figure, but want this to be done automatically because I have several curves of the same type in a loop.
R data.table select set of columns for maximum value based on group
I saw multiple posts to meet my requirement, but some how not able to get the needed result.
I have a data.table with multiple columns. Out of all the columns I want to select few columns for their maximum value and summarize them by the group variable.
Below is how my sample data -
library("data.table") set.seed(1200) ID <- seq(1001,1100) region <- sample(1:10,100,replace = T) Q21 <- sample(1:5,100,replace = T) Q22 <- sample(1:15,100,replace = T) Q24_LOC_1 <- sample(1:8,100,replace = T) Q24_LOC_2 <- sample(1:8,100,replace = T) Q24_LOC_3 <- sample(1:8,100,replace = T) Q24_LOC_4 <- sample(1:8,100,replace = T) Q21_PAN <- sample(1:5,100,replace = T) Q22_PAN <- sample(1:15,100,replace = T) Q24_LOC_1_PAN <- sample(1:8,100,replace = T) Q24_LOC_2_PAN <- sample(1:8,100,replace = T) Q24_LOC_3_PAN <- sample(1:8,100,replace = T) Q24_LOC_4_PAN <- sample(1:8,100,replace = T) df1 <- as.data.table(data.frame(ID,region,Q21,Q22,Q24_LOC_1,Q24_LOC_2,Q24_LOC_3,Q24_LOC_4,Q21_PAN,Q22_PAN,Q24_LOC_1_PAN,Q24_LOC_2_PAN,Q24_LOC_3_PAN,Q24_LOC_4_PAN))
Now for the above data I want to select 4 columns for their maximum value by region. So the result should have the ID variable, region variable and these 4 variables with 10 rows. 1 row for each region. I tried below code but it creates a column as mycol and puts the value of the 4th element of vector mycol
mycol <- paste("Q24","LOC",seq(1:4),"PAN",sep = "_") df2 <- df1[,.(mycol = max(mycol)),by=region]
Please suggest where I am going wrong and how I can achieve this.