Last 50 Posts on Chandoo.org

Chandoo on 2024-04-17 20:54:53
You need to join the teams community to participate in this contest.

Mohammed Vasiuddin on 2024-04-16 11:58:30
Hello Chandoo where is the data file set, is it available in TEAMs community.

Ali on 2024-04-15 08:50:09
=SUM(MID(B4,SEQUENCE(LEN(B4)),1)*1)

Chandoo on 2024-04-10 22:41:13
Sorry to hear that. Do you have teams on your computer or phone? If so, click the link and try again please.

Vignesh on 2024-04-10 10:13:33
Not able to join the community and teams link is not working. Page loads and does not show any screen

Xav on 2024-04-09 22:04:10
I'd love to practice this example, but I can't get very far. I think the problem stems from the very beginning before any steps or transformations have taken place. I can see on your "Combine Files" photo, which shows the sample "travel details" file, that you have two columns (Column1 and Column2), with Acme Inc Employee in row 1. I don't have that. My sample "travel details" file shows Acme Inc Employee as the column 1 header; the column 2 header is Column2. As soon as I move on to any step that removes Acme Inc Employee as the column 1 header, I get this error message: Expression.Error: The column 'ACME Inc. Employee Travel Expense Report' of the table wasn't found. I don't see that type of error addressed in your error list. What would be the solution to this error? Thx.

Ryan on 2024-04-09 10:06:59
Hi, Very cool spreadsheet thanks! is it possible to change the automated colours used. For example making different icon options (anniversary, work, holiday) appear in different colours other then pale blue and orange for holidays? Thanks.

Jim on 2024-04-02 19:35:26
Have you ever had to do this using Power Query? Or, know of a way to do something similar, but using Power Query? I have a huge workbook that uses a method similar to yours, but it's way to slow using the SMALL and ROW formula so I'm trying to speed it up, but by using PQ. Thank you so much in advance for any help!

sivanantharajah on 2024-03-29 05:40:36
how can calculate in excel format this 1 pipe 19.5 feet price 20478 Rs , Amount foe 3.4 Feet pipe price pls thnx

Erik Severius on 2024-03-27 08:56:18
Hi Chandoo, i'm trying to use this function, but the result stays blanc. So i don't see the bold string. Not after following the video nor as i copied the function from above. :( Since i am using the Dutch language version i changed 'Bold' into 'Vet' wich is the Dutch word for it, and now it works!!!.. Thanks for this function. I will be waiting for a function where i can calculate with coloured cells :)

Chris on 2024-03-26 17:05:15
THANK YOU SO MUCH!!! I have been looking for this formula for days! I had it years ago and forgot it, found it, then accidently deleted it from my document.

Henry P on 2024-03-26 04:59:14
The number formula works in Excel Less than 1 is a decimal and a whole number is displayed without any zeros or decimals. Would like the same format in Microsoft Word when entering a quantity into an invoicing field. To show less than 1 in a decimal and for whole numbers NO zerros or decimals

kayla on 2024-03-24 21:36:01
Thank you! do you know why when i save this to google drive it says "Unsupported named ranges will be removed" when I try to edit?

Chandoo on 2024-03-21 04:12:25
You can use XLOOKUP for such things too. For example, if you have three columns: home phone, cell phone and email address and a column with customer name and you want to lookup the name of the customer when you specify any value from one of those 3 columns, you can use the below XLOOKUP. =XLOOKUP(TRUE,BYROW(C3:E22=I2,LAMBDA(a, OR(a))), B3:B22, "No record found!") Here I2 contains the search criteria (either home phone, cell phone or email) B3:B22 have names C3:E22 have the home / cell / email values

Dorothy Adams on 2024-03-21 03:52:21
You show return array can be more than 1 column but what about Look up array? What if I want to find a value than can be in 1 of 3 columns and then return one value from another column.

Santy on 2024-03-19 11:16:06
I added a col with numbers to refer to the row and used a simple Xlookup formula. It works fine. Am i missing something ?

Chandoo on 2024-03-18 19:53:58
Good point. I suggest using the free MS Office online (you just need onedrive account) to maintain old files and work on them. The only limitation is that it is browser based, so you won't be able to do many advanced things. But it is better than the alternative of shelling out $100+ every year.

Brian Crawford Wilson on 2024-03-18 10:55:03
I agree with Ron I have MS Office 2019 which I used for Charity work but a pensioner I find the cost of the MS365 unaffordable. Perhaps there is some way for a Ms Guru to perhaps create 3rd party update for the stand alone versions. I will however continues with Ms 365 this year as I have just renewed the subscription thanks very much for keeping us abreast of latest developments and also the excel community for their useful feed back regards Brian 18/03/2024

Ali on 2024-03-17 09:47:09
This works too: =LOOKUP(1000,SEARCH(resTypes,B4),resRates)

Ali on 2024-03-17 09:16:40
=XLOOKUP(LOOKUP(1000,SEARCH(resTypes,B4),resTypes),resTypes,resRates,"",0)

Chandoo on 2024-03-16 17:06:16
You can change the year value of the calendar to 2025 and it works automatically.

kayla on 2024-03-15 16:39:59
Thank you so much for this! Is there a 2025 version available yet?

kayla on 2024-03-15 16:39:34
Thank you so much for this! Is there a 2025 option yet?

Chandoo on 2024-03-13 19:48:56
The time returned by Excel's NOW() is limited to the environment. So if you run it on a PC, it will be same as your computer clock. Excel currently has no simple way to figure out the timezone of the computer or user.

Chandoo on 2024-03-13 19:44:09
Can you check your data? The year function has no such limitation. It should process an array and return an array in Excel 365.

Yamini on 2024-03-13 14:46:20
Hi, I tried using this Filter function in the same way as described in this article, but in Microsoft 365, I did not get the same response, I wonder if Year function which is used inside of Filter function is capable of taking a range of values? Because Year() only takes a single value and returns the year. Please clear the confusion here.

Rash on 2024-03-13 09:24:32
How make this formula "=IF(H4"",IF(I4="",NOW(),I4),"")" to get the UTC time , as the cell would be updated by teams in different time zone

Rebekah on 2024-03-12 06:43:59
All of these still did not solve my problem, and then I wondered if it is because I have iterative calculations enabled (I need that for another part of the worksheet). I switched it off and it solved my issue. (It's under Excel Options > Formulas.) So to do calculations I need to turn this off, then turn it back on again afterwards.

Yohannes Teshome keneni on 2024-03-10 10:00:38
Hello, Chandoo I am Yohannes Teshome from Ethiopia found at horn of Africa I have been watching your tutorials more than 4 months They were easy to understand and they increased my interest on being a data analyst... thank you very much chandoo...

Musa Banda on 2024-03-07 22:05:18
Amazing Chandoo.