formula

  1. D

    Extract text between MULTIPLE parentheses in one cell

    Hello, I would like to extract text between MULTIPLE parentheses in one cell (without using VBA). The blue text in the picture below is what I'd like to achieve automatically. When there is just one set of parentheses, it's easy. For B1 I could use...
  2. V

    how to add one in JK-1050 so that it will be JK-1051, if some condition meet

    Hi All, Thanks in advance, we have the following sheet Sheet1 AB1S.no.number21JK-1050324354 Sheet2 ABC1S.no.stno.21noJK-105032no43o.k.54 If Sheet2!c2 is match with Sheet1!B2 then in Sheet1!B3 JK-1051 else blank, required a arrayformula if possible
  3. L

    How do I make one cell always equal to a specific number while still taking into account of other cells.

    I need a formula which allows the 'Money Leftover' cell K7 to always equal 500. I've probably worded the above really vaguely, however I'll add an image to try and get explain myself. What I need: As I start to add in values under columns C-I, the values in K will either go up or down...
  4. K

    Display on pivot table a count of only those column values which has a unique ID (in a separate column)

    Explanation of my source table: I have column E which has data-validation dropdowns for Reviewer numbers, and column R which has traffic transaction details. Reviewer now check each of the transaction (Column R), and once review is done, will click on the dropdown (Column E) and select Reviewer#...
  5. S

    Highlight entry based on list

    I have a list of 5,000 contacts created for an email blast that includes email address, first and last name, and company name. I have a separate list of company names I do not want to receive an email. I want to create a formula (or conditional formatting?) that would compare the 2 lists and...
  6. B

    The smallest range with the highest number of data

    I have some data like this: DATA 1 1 2 5 5 9 11 14 14 18 18 29 29 30 37 38 40 40 61 62 2 2 4 4 6 9 9 10 12 15 16 17 30 31 35 40 41 45 50 60 1 4 5 5 9 9 10 10 11 25 25 28 30 35 45 50 51 57 60 68 The values are up to 100. No bigger values. Each...
  7. V

    Arrayformula with time delay

    HI All, We have the following sheet Head 1Head 2116004265606-07-2020 13:060.00149306212065265606-07-2020 13:080.0056713124004265606-07-2020 13:16 required a formula who can do the calculation in A column. A3=C3-C2 A4=C4-C3 have a lots of date in the cell so need a array formula and you can...
  8. V

    if or filter or any other formula/code

    Hi All, Thanks in advance, we have the following sheet Item CodeWork 1Work 2Work 3Work 4P-1P-2P-3P-1P-2P-3P-1P-2P-3P-1P-2P-3A-405fsxvxvA-820fdsfdfdfokvxvxA-440fdsffdsokvxvxfsdssfokfdafA-650vxvvxvokxcvvgsdsokvcxxvvxcokgdgA-260cxzccxok vcxvxvxcok cvvvxcvokdgdggdgdgok and want to do following...
  9. S

    Tricky budget sheet code

    Hi, I have created a custom budget sheet that we will be using in our organisation and have hit a road block with what I would like it to do next, and hope that someone may be able to offer some help. I have scoured the internet and tried to work this out but my knowledge is limited! Thank you...
  10. V

    if with some condition

    Hi all, we have the following sheet, and want to change the color if the condition meet according to E MAX LEVELQuantityExtra15001600Normal50035060 - 40% balance20071BELOW 30%10025 help pls
  11. E

    Split results of query array, apply different formulas to each then send to Worksheet?

    I have a macro that uses a query to open a tab delimited text file and import some (but not all) of the columns into a specific worksheet in the same workbook from where the macro was called. This works perfectly except I need to run the data from each column through a formula that cleans up the...
  12. B

    Need a formula - filter so 2 columns totalling more than or close to another column only show

    Column I in my excel file shows unpaid invoices. Column K shows uninvoiced items. Column P shows the customer's balance on account . Most of the time, P should be greater than I+K. However we want to be able to easily spot the customer files that are close to or have exceeded the customer's...
  13. D

    MIN&MAX works for 18/19 but not 19/20

    I have a spreadsheet that for each item calculates the number of days that fall within the financial year and then the cost that would relate to it. The formula is: =I10*((MAX(MIN($Q$1,H10)-MAX($P$1,G10)+1,0))/365) Where:- P1: first date in the current financial year being reported -...
  14. M

    Formula for calculating contribution to change/result

    Hi, I hope I manage to explain this in a way that makes sence. I have two departments which are rated by customers every week. Calculating the change in average score for each department is simple enough, but I need a formula that calculates hos much each department has contributed to the...
  15. C

    Conditional Formatting

    Hi All Hard to explain but ill give it ago Column A is activity and column B is Percentage Column A can have the same tasks but for different departments e.g Relocation - HH or Relocation - NHH But column A can have individual tasks. What i need is, if the tasks percentage is NOT 100% then...
  16. W

    If a date is between 2 dates annually

    I am looking for a formula to determine if a date in column D is between the first day of October through last day of May from 2016-2021. I am looking to do this over several different years (Oct 2016-May 2017, Oct 2017- May 2018, Oct 2018- May 2019, etc.). I have a master spreadsheet with...
  17. V

    if and match or find - any code or formula

    HI All, Thanks in advance, we have the following sheets Table 1 s.nohead 2head 315246359252658936598544632541859584764 Table 2 s.nohead 5t1t2t315246359525.01.2020text 1Rozi D mario2958476426.01.2020text 2Dino Mario365985456927.01.2020text 3Suchil452658928.02.2020text...
  18. H

    Editing multiple cells

    Hi, I have some 50 number of cells filled up with digits.. Now each cell I want it to convert it in to formula, so whatever figure it contains i want to add "=" before it and "/24" divide by 24.. doing same thing 50 times is cumbersome process. Could anyone guide me how to do it quickly.
  19. A

    How to tweak a reference so that when it's copied one column over, the reference jumps by five columns?

    Let's say Cell A1 in "WorksheetA" references Cell A5 in "WorksheetB". When you copy Cell A1 to Cell B1, its formula would of course typically adjust to reference Cell B5. However, let's say I'd instead like its reference to adjust by five columns, not one column. (That is, without changing...
  20. V

    subtracts time array formula

    Hi All, Thanks in advance, we have the following sheet, we want to subtract B3-B2 in A3, 06-07-2020 13:160.00298611106-07-2020 13:2106-07-2020 13:2406-07-2020 13:2806-07-2020 13:36 pls provide any array formulla

Some videos you may like

This Week's Hot Topics

Top