In Excel: The field length to be calculated has this value in cell C2: 01/09/2017 00:02:01 The formula used =LEN(C2) The result returned is 15 The correct number of characters is 19 Format of cell C2 is a custom value: "yyyy"-"mm"-"dd" "hh":"mm":"ss" When I use this in Google Sheets, the exact same steps, I get… Read More Excel LEN formula returning incorrect value for a date field

I need to search the ‘ITEM NAME’ list for matching size and set MRP based on it. For Example- NIKE PAHANTOM 38 RED”s MRP needs to be set 320 NIKE PAHANTOM 44 RED”s MRP needs to be set 400 >Solution : Using XLOOKUP() would work: =XLOOKUP(–INDEX(TEXTSPLIT(A8," "),3),$B$1:$E$1,$B$2:$E$2,"Not Found") Or, if the pattern remains same then… Read More How to search for size in the list and set costing based on it?

How should i generate a list of names using input data (marked with green in image) Please check Image for example. >Solution : Try using the following formula: =TOCOL(B2&" "&B3&" "&TOCOL(B4:N4,1)&" "&TOROW(B5:N5,1))

when collecting my data I organised my variables wrong but it is not a transpose issue. I tried xlookup but one of my arrays is a row instead of a column. I suspect it might be an index job but I am not familiar with it at all. This is what it currently looks like:… Read More I need to reorganise my excel spreadsheet so i can run some regressions on Stata

No idea how to title this one. Suppose I have the above table. I want the column "used in" to display the name of every food that uses the food on this row, to look like this: The separator is unimportant, I just used a ; as I like how it looks. How would I… Read More Display cells that contain X in excel

I need to calculate the number of holidays people have taken within a given date range, but I’ve got no idea where to start. Any help would be appreciated. >Solution : Try something along the lines of using : =LET( _Holidays, XLOOKUP(P5,H$5:N$5,H$6:N$14), _StartDate, DATE(G$2,E$2,C$2), _EndDate, DATE(G$3,E$3,C$3), SUM((_Holidays>=_StartDate)*(_Holidays<=_EndDate))) Using BYROW() one can spill the output using… Read More Calculating number of holidays taken in a selected period

I have an existing formula (which I didn’t write), which calculates the statements in BLACK. =IF(AND(A4="BIN TO BIN", B3=B4,A3="BIN TO BIN",LEN(F4)>=4,OR(AND(OR(RIGHT(F3,1)="A", RIGHT(F3,1)="B", RIGHT(F3,1)="C", RIGHT(F3,1)="D"), OR(RIGHT(F4,1)="A", RIGHT(F4,1)="B", RIGHT(F4,1)="C", RIGHT(F4,1)="D")), AND(ISNUMBER(VALUE(RIGHT(F3,1))), ISNUMBER(VALUE(RIGHT(F4,1))))))=FALSE,"",TRUE) I need to add the condition in RED, but I’m unsure how to go about it. Thanks for your help. >Solution : =IF( OR( AND(… Read More Adding a condition to an existing IF/AND/OR formula

I have a list of several hundred product codes (alphanumeric) and I’m trying to get a list of the top 20 i.e. the 20 items which occur the most. I’m only able to make a formula that gets the single most popular item. I’m struggling to figure out how to extend it to include the… Read More EXCEL – Listing top 20 items from a range

I’m trying to write an excel formula that allows me to filter results searching across all columns and sheets in my document. For example, let’s say my excel file has two sheets (Sheet1 and Sheet2) with the following data: Sheet1: A B C Col1 Col2 Col3 foo 1 NA bar 2 y baz 3 bar… Read More excel formula for searching string across columns and filtering results

I’m using the following formula to list unique values in a column, excluding the last non-empty cell in the column. =UNIQUE(AV6:INDEX(AV:AV, MATCH(2, 1/(AV:AV<>""), 1)-1)) It works great, as long as there is no data in cells below it. For example, it fails if there is an empty cell just below and then data below that… Read More UNIQUE Detecting First Empty Cell & Excluding Last Non-Empty Cell Just Above It