This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Problem description & analysis:
In the following table, value of cell A1 is made up of names of several people and their attendances in four days. For example, c is 1100 expressed in hexadecimal notation, meaning the corresponding person has attendance in the 1st day and the 2nd day and is absent in the 3rd day and the 4th day.
We need to find the number of people who has the attendance in the day input in A2. For example, three people are present in the 1st day and two people are present in the 3rd day.
Solution:
Use SPL XLL to type in the following formula:
=spl("=theDay=shift(1,?2-4),?1.split@c().step(2,2).count(and(bits@h(~),theDay)>0)",A1,A2)
As shown in the picture below:
Explanation:
shift()function performs a shift operation on an integer. step(2,2) gets members at the even positions. bits@h parses a hexadecimal number. The and operator represents and "AND" operation with bits() function, and ~ represents the current member.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-06-24T06:39:16+00:00) In Excel, Parse Hexadecimal Numbers And Make Queries. Retrieved from https://www.scien.cx/2024/06/24/in-excel-parse-hexadecimal-numbers-and-make-queries/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.