# excel & formula

1. ### If A without B, then C / Employees currently on break based on break list schedule.

Hi, I'm making a break list at my new job for my team. Because the list has multiple entries we are forced to concentrate unnecessarily to calculate how many agents are on break. It would be helpful for my colleagues to see how many agents are on break right now, since we have a rule of no...
2. ### Check if the in and out time falls between a specific time interval

I am looking for a solution after a long trial and error workings. I have 2 columns in and out with date and time. I want to check if the in and out falls between the interval 10PM to 8AM of next day. Further, for those which falls within this range, I want to check atleast minimum of 3 hours...
3. ### Excel formula does not self adjust.

Hi, I have following cells: Original Value Adjusted Value A A A B B In column B, "Adjusted Value", I have the following formula in each cell to eliminate any duplicates in column A: =IF(A2=A1,"",A2) =IF(A3=A2,"",A3) =IF(A4=A3,"",A4) The problem occurs when I delete row 3 (the...
4. ### Using "OR" statement with DATEDIF

Good morning! I need help with a DATEDIF formula. This is my original formula: =ROUND(IF(YEAR(AC93)=2018 & MONTH(AC93)=8,"0",DATEDIF(AC93,"8/1/2018","m")/12),0) It works perfectly when the date in cell AC93 is 8/1/2018, which is what most of the 1,000+ employees I'm calculating years of...
5. ### importing data in excel from external source and autorefresh every 15 min. enabled but not getting data refreshed

I have imported data from external source, that data gets automatic refresh after 15 minutes I have such 10 sheets in one workbook. when I am trying for 5 sheets it works very well but when i try for 10 sheets in one workbook it just shows "connecting to web" and does nothing. kindly help with...
6. ### Custom format cell to consider dash as zero in excel

I import data from web external query in excel in which zero value represented as dash, to further calculation on this I need to consider this dash as zero for that I have macro to convert dash to zero but, data gets refreshed every 5 min. so I need to run that replacement macro every 5 min...
7. ### Trying to sort dynamic table in order of number value of first column (this column contains duplicate values)

Hi, I have a dynamic table that I'm trying to sort by the value of the cells (that have duplicate values) in the first column A. I would start of with this: <tbody> Column A Column B Column C 3 cat Cats Rule#1 3 cat Cats Rule#2 3 cat Cats Rule#3 1 dog Dogs Rule#1 1...
8. ### Excel large sheet: Want to bring right side coloumns visible when needed

Dear Friends I am new to this forum and need a solution to my problem: I have a large sheet and have about 50 headers on coloumn on right side. I need not to scroll to right side and need to bring each coloumn in front of me with one click. At the same time I need some fixed cells always in...
9. ### Count If Visible Fields Advice

Hi all, I have this formula which provides a percentage of column B count for a Yes or No answer. =COUNTIF(B6:B6345,"YES")/COUNTA(B6:B6345) =COUNTIF(B6:B6345,"NO")/COUNTA(B6:B6345) I need to know if there is another formula I could amend the above to so it counts visble fields only after i...
10. ### Formula to list all possible combinations of 4 rows in excel spreadsheet

I have a spreadsheet with 4 columns of data. I am trying to work out a formula to work out all possible combinations of the 4 columns. Each column has a different amount of data. It is text data. I had a working formula but Ive somehow broken it :mad: I want the output to contain all 4 of...
11. ### How to Re-Write a Very Long Formula

Hello, I have a spreadsheet with tens of thousands of formulas like the following. I've split it over multiple lines for readability. =IFERROR(IF(K156=0,0, IF(\$J156<=AC156,AC\$2, IF(\$J156<=AD156,AD\$2, IF(\$J156<=AE156,AE\$2, IF(\$J156<=AF156,AF\$2, IF(\$J156<=AG156,AG\$2...
12. ### Performing a SUM within a COUNTIFS

I have a database in excel where columns hold lots of different pieces of data. On a different sheet I would like to be able to perform a SUM within a COUNTIFS statement. The logic in my head is this, but obviously it does not work...
13. ### Compare two week numbers from different years in Excel

I have some calculations that are strongly dependent on "this week", "next week", etc. Let's say I have a date (Column A). I can use <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height...
14. ### How to create double vlookup in Excel?

I'm gonna search for Country Code for the criteria below: https://zapodaj.net/2f9592ebe0b99.jpg.html I do not know how to use neither double vlookup nor index-match. Can You please create both formulas and explain shortly how it works? Thx in advance. ;)
15. ### Help needed with a formula that Skip the predefined Value and Ignore the duplicate contents in a row

Hi, I am creating a spreadsheet with several values (one Text value in each cell) i.e. <tbody> Row1 Col. A Col. B Col. C Col. D Col. E (The Result) Row2 Apple Banana NA Pumpkin NA Row3 Cherry Apple Mango Pineapple NA Row4 Apple Banana NA Pumpkin Apple </tbody> I wish...
16. ### VBA: Find & Find next function: Return multiple value in a single cell if the ID match

I am using find function to match ID in Report Worksheet with the ID in Data worksheet and return the data to the ID in Report Worksheet if there is a match. For unique with multiple match, the code is only returning the ID data which every is last match in the search range in Master worksheet...
17. ### Multiple within the same column with criteria

Hi, I am wondering if there is anyway to calculate the Product for column "value" based on category with just using excel formula? For example, based on the table below. I want to find the product for category A = 1*2, category B = 3*2*2 and C = 3*1*2. I tried using SUMPRODUCT but instead...
18. ### Need a VBA Macro to hide/ unhide rows based on a string in a cell value

I have a column (A) that has below values in different cells (A1, A2 and A3 and so on). PPC Revenue - 2015 Non-PPC Revenue - 2015 ROAS - <Enter PPC 1 Name> - 2015 ROAS - <Enter PPC 1 Name> - 2017 Non-PPC Revenue - 2016 Non-PPC Revenue - 2020 I need a macro that can be used to hide/ unhide...
19. ### Vlookup unable to return the next cell that meets criteria

I'm trying to build a table that lists a broker's volume from largest to smallest (top 10 firms only). If there are only 9 values in the data set, a zero will appear in the row. To grab the volume, from largest to smallest, I'm using =LARGE(\$B\$2:\$B\$63,1), =LARGE(\$B\$2:\$B\$63,2)...
20. ### Calculate a cell value based on a specific string in a separate cell

Hello, The first column contains a string of values as mentioned below . In 2nd column, I need to strip out specific info from Col 1 (as mentioned below). I tried using a formula like this - =LOOKUP(MID(AS2,12,3),{"Lar","X-L"," Sma"},{"Large","X-Large","Small"}) , but its not calculating the...