turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 175K Members
- 4,612 Online
- 43.8K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

4597 Conversations

- Mark all as New
- Mark all as Read
- Pin this item to the top
- Subscribe
- Bookmark
- Subscribe to RSS Feed
- Invite a Friend
- Sort by Conversation Start Date

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Custom List Message Item | |||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

Hi All,
Apologies if this has been posted already, but I couldn't find on the previous answers precisely this. I am creating a file that calculates Overtime of our staff. As per our local legislations, if a worker overtime falls AFTER 4PM and BEFORE 4AM, h ... Read MoreHi Thomaz,
To compare the time you shall use its number representation, e.g. 16:00 is equal to 16/24. If you use time only (without the dates) that could be like =IF((A... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi All, I'd need an additional help please. If I have a string containing this text : [09OCT18-16DEC18^30DEC18-01JAN19^12JAN19-31MAR19]
I'd need to read it like "from 09OCT18 to 16DEC18 and from 30DEC18 to 01 JAN18 and from 12JAN19 to 31MAR19", is there ... Read MoreHi Macro,
This can be done by using Power Query which is also known as ( Best Response confirmed by Marco Verace (Occasional Contributor)
| |||||||||||||||||||||||||||||||||||||||||||||||||

I’m creating an order form in Excel to be used by clients. The idea is that the client will select a product from a dropdown list in the first column of the table (tbl_Product_Order) and then in the same row insert the width, depth, height and quantity of ...
Read More
Hi Pieter, Please check this https://www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function if help. Another way is to use Evaluate() function within VBA c ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Good day! As stated above can someone help me. I am using index match but I find it hard because it wasn't in a same column.I want to get the specific data for example I select 1 or any number. Please take a look at this. https://www.dropbox.com/s/aheew349r8x4rvv/Error.xlsx?dl=0 Read Moreyou want to return in the printing sheet multiple values right? | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi guys! - 21JAN19
- 22JAN19
- 23JAN19
- 31JAN19
Please see attached workbook that i have embedded the formula.
Best Response confirmed by Marco Verace (Occasional Contributor)
Another question how many such ranges could be (here we have two) Your logic does not seem to be correct.
your range does not have 31 Jan 2019 then why to list 31JAN19 in the missing dates? | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi
I am using excel 2016 on mac.
As I finished working with one row of numbers in excel, I change the colour of the whole row to black. It makes it easy to quickly identify the next row I am working with. As I put in values below the row that is now black ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

So I have prepared a (for example) drop down menu for specific cells. What I'm trying to do make the chosen value (from the drop down list) to automatically assign a predefined value into the cell next to it.
So hypothetically...
Say cell D2 has a drop ... Read MoreI think you may consider a table contains the type of configurations and the corresponding values. And then use vlookup to show the value.
For example: Column AA: list o ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I use the TODAY formula to track the amount of days a person is located in a specific room. I base the starting point as their date of registering into the room and track the amount of days they are in the room with the formula assuming C3 is the cell wi ... Read MoreThen you could use this in E3 =if (D3 = "", TODAY(), D3) - C3 Or to be a little more advanced... =if (C3 =... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

At the moment I often use formula like this:
=IF(ISNUMBER(AT24),AT24/AW$32,"")
(check to see if a number is on cell AT24 and if there is then divide it by AW32 and give me the answer. Otherwise leave it blank.)
I want to use these formulas to show the di ... Read More
Hola te puedo hacer las preguntas en español?
| |||||||||||||||||||||||||||||||||||||||||||||||||

I have a table of data (test scores) for students at the top is the maximum score they could have achieved for each test. On the left side I have average all their test scores to give a grade. (see image below)
However, I realised that since all the te ... Read MoreHi Ewan,
Could be like =SUMPRODUCT(E7:G7/E4:G4)
| |||||||||||||||||||||||||||||||||||||||||||||||||

Trying to create a small table that references data from another table on the same worksheet by using the formula =A2. Certain cells are reading the formula instead of the cell data. Using Excel 2013. How do I correct this issue. It sounds like some cells are formatted as text. In the Home ribbon change the type from Text to General then double click in the cell and press enter Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi, new in this forum and lives in Sweden (guess that one is a lead on the problem, Sweden I mean).
Writing a macro (as newbie) and stumbled into a problem working with constants containing decimals (exchange rates) in values. Key parts of macro:
Dim sCurr ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi,
I can't seem to find something in the help which matches what I'm looking for. I have a workbook with a worksheet of input parameters, one of which I want to use to select a worksheet with a range of numbers which is then used as an input range in an ... Read MoreHi Simon
You could try INDEX (see attached) with a formula like this
=INDEX( INDIRECT($A$3&"!$A$1:$AV$365"), ROW(A1), COLUMN(A1) ) Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I have a few numbers. I want to get them together with a "|" to separate them. I want to get the result as the screenshot below. Is this possible? Many Thanks
Yes you can with TEXTJOIN function. lets say your data is in range A1 to A16 then use the below formula. =TEXTJOIN("|",TRUE,A1:A16)
If you do not have Office365 then yo ... Read MoreBest Response confirmed by Jamil Mohammad (MVP)
Hi,
If you are on Office365 subscription the easiest way is to us TEXTJOIN like =TEXTJOIN("|",TRUE,A1:A10)
| |||||||||||||||||||||||||||||||||||||||||||||||||

I want to receive an alert when a document lacks 30 days to expire and that continue to indicate it during those 30 days. If C9 is greater than -30 but less than 0 is about to expire. Hi,
I got two solutions for you! First one by using a formula, and the second by using VBA.
The formula solution will notify you in another cell on the same worksheet by d ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi,
Here is how the sheet looks so far
So my problem is I am trying to work out how many more cleansed items I need ... Read MoreHey Ricky-
Just wanted to follow up and see if one of the two solutions provided were able to get the end result you were after. If not please let me know and I'm happ ... Read MoreHi, to both!
You could use this formula too (non CSE) in E2:
And drag it ... Read MoreMaybe try using the User Defined Function (UDF) below:
Function GetCleansed(Cleansed As Integer, Total As Integer, PercentTarget As Double) Dim x As Integer... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I have a table with one column venue description for venues the text is all in one paragraph and i want to break it up evey 20 words at the next word after the nearest fullstop (so if fullstop is at 22 then after 22 words)
Hi @paul marsh
Show one example. Coz whether you want split that data in next column or another one?
Make it clear. Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

how to freeze the drop down in excel file. when i'll select one column then 2nd column value should automatically come. if anyone have solution plzz advise
1.Did you mean about Freeze Panes? (i.e. Freezing Rows / Columns while scrolling) 2. On which basis you want to bring the value second column.
Make it cle ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi Alex,
That could be =IFERROR(INDEX(A:A,MATCH($C2,B:B,0)),"") for E2 and drag it down Best Response confirmed by alex alex (New Contributor)
| |||||||||||||||||||||||||||||||||||||||||||||||||

Hello,
I need help with a formula in Excel. I need to count the Overdue deadlines in a sheet. A deadline is "Overdue" when the date Today is later or when the cell next to it is still empty after this date.
So it nee to be someting like this:
Hi Petra,
Formula for C3 could be =IF((A3>TODAY())*ISBLANK(B3),1,"") or simply =(A3>TODAY())*ISBLANK(B3) if you use 1/0 in C3
Above is for the combination of A3 and B ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi I am trying to see if I can do a formula to auto populate column b
So if I select apples from my drop down in column A 37 will automatically populate in column B
Hi Tina,
It looks similar to this one https://techcommunity.microsoft.com/t5/Excel/automate-data-entry/m-p/198113#M6225 Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Apologize in advance if my request seems confusing. I need a formula that indicates the "end date" of when I run out of the content pieces on column F. This is based on the "start date" and the fact that these pieces are distributed only on Monday and Tu Try the file I attached; I added a new sheet for the calcs, you should hide it if you don't want to mess with it.
In that second sheet, there are all datas from the start ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I'm sure if this is do-able in excel, but what I would like to be able to do is pull a number from a master sheet which relates to a name (column A contains the name, column B contains the number) and enter the number next to the name in a new sheet or wo ... Read MoreTry using a vlookup formula (see attached .xlsx file):
=IFERROR(VLOOKUP(B2,Table1333463[[#All],[Horse]:[Master]],2,FALSE),"No Data")
Hope this helps!
Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hello, I have been using many different methods for attempting to convert text and date - but failing miserably!
Essentially, the way I can download data provides dates that appear like this:
Jun 13th 2017 10:44 Depends on regional setting and defined long date format, for US ones could work =DATEVALUE(SUBSTITUTE(A1,MID(A1,7,2),""))
Do your months normally show in English when you enter a correct date in Excel and format it to show the name of the month?
I suppose it is a string.
If so, you may consider LEFT and MID function to extract the information about year/month/day. Then, use the function DATE, you can have the da ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I am using =IF(ISNUMBER(SEARCH("RangeName",G2)),RangeName) to find words in a cell for list of words. Once that word is found I want that word to be the result if the words nor found than say FALSE. I used this formula in 7,000 cells only 1 cell came back ... Read MoreTry using a formula like the below. I have attached an example file for additional reference:
=IF(ISERROR(SEARCH($C$2,F2)),"FALSE",$C$2)
Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi,
I'm new into excel and doing some digging I cant find an appropriate solution. I am currently building a registry worksheet where I will log everyone that booked.
These are my three columns; 1) Early-Bird Discount Applies? 2) Price Level 3) Days booked
4 ... Read MoreI have an idea. But I think it is not good.
Since the first three columns have fixed choices. So I will first concatenate the value in the first three columns. For short ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I have a data set (15,000 rows) in excel that looks like the sample below. Is there a way get to some thing that looks like this? Basically I want to count the entries by customer, get the last date, and output in one row. | |||||||||||||||||||||||||||||||||||||||||||||||||

The IFS statement below works with the exception of the
=I ... Read MoreErin-
It's difficult to tell what you're trying to do with the formula alone. Is there anyway you could please provide a non-sensitive example file to give a little co ... Read MoreHi Erin,
For the logical test instead of SEARCH(...) use ISNUMBER(SEARCH(...)) | |||||||||||||||||||||||||||||||||||||||||||||||||

How would I solve for an growth rate using two inputs: today's value (payment) and the sum of a stream of payments?
For example: what growth rate is required to make today's value of $100 equal $700 over the next 5 periods?
I can use the backsolve funct ... Read More
Hi Mark,
When you say you are using the backsolve functionality, are you using Goal Seek? | |||||||||||||||||||||||||||||||||||||||||||||||||

You can use the Concatenate Function or the & Operator like follows:
=B2&A2 =B2&" ("&A2&")" =CONCATENATE(B2,A2) =CONCATENATE(B2," (",A2,")")
Please see example f ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hello,
I am trying to create a formulae that allows automatic calculation of an employee's incentive bonus.
An example of what I am trying to create is as follows:
The employee will receive graduated incentive bonus monthly once their generated revenues r ... Read MoreDear All, Could you help me to creat a formulae in Excel sheet as below;
If the the target achieved 100% the customer will get 1% andIf the the target achieved 120% the cus ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi,
I am using pivot tables to look at data from a large set and I want to sum the largest 5 values in a pivot table column and calculate it's % of the total of that column. If the range was static then I would just use SUMPRODUCT(LARGE(AD5:AD22,5)) and ... Read More
You could define a dynamic range over the pivottable and use the dynamic range as the argument for the LARGE function. Alternatively, why not define a second pivottable t...
Read More
Best Response confirmed by Chris Sanders (Occasional Contributor)
| |||||||||||||||||||||||||||||||||||||||||||||||||

Hi everyone,
I currently haveca pivot table set up with a slicer and would like to apply conditional formatting to this but whenever it's filtered the formatting stops. Is there any way to fix this? I'm currently attempting to try and use a formula to selec... Read More With Pivot Tables the best thing to do is go to Home > Conditional Formatting > New Rule
Then you get extra options appearing at the top of the window Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hello,
I need assistance with an Excel formula, please. I've been struggling with this for hours; your help is greatly appreciated.
I have a spreadsheet with 3 columns. The three columns are entitled "Month", "Year" and "Amount." There are about 10,000 ... Read MoreHi,
Please try this formula: =MAX(IF((E4=A2:A20)*(F4=B2:B20),C2:C20)) And find it in the attached file.
Hi
the easy way would be a pivot table. "Year" and "Month" in row area and "Amount" in values area. Change the aggregation function to "Min". Or use MINIFS(): =MINIFS(C2:C1... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi guys,
So basically what I am trying to do is let Excel determine if what tier we are going to use automatically so it's like this:
If the given weight (column B) is less than, equal to, or greater than the range in column I, then it should correctly se ... Read MoreMichelle-
I believe you can accomplish your task by using a Vlookup. Please reference the below post and the example file that is included in it . You will need to ma ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I have attached my spreadsheet to help clear up what I am doing. Basically i want this formula to look at sales/m (sales per month) then check the sale $ (price), and return a buy price based on a certain margin range. the Margin % range and test columns ... Read MoreKatrina-
Try using a lookup table instead it will greatly simplify your formula (I've attached an example file for your reference):
=IF(A2>20,ROUNDUP(B2*VLOOKUP(B2,$K ... Read MoreBest Response confirmed by katrina bethea (Occasional Contributor)
| |||||||||||||||||||||||||||||||||||||||||||||||||

I do spreadsheets for my work and on my new windows 10 when I set the column width and save and then reopen, the column widths are no longer what I set up. I make the corrections, save and close and when I reopen they are wrong again. I am confused, nev ... Read MoreMine are doing the same thing starting a couple weeks ago after the windows update. These are spreadsheets I have used fine for years. Suddenly they reset every time I op ... Read MoreDo you have a non-sensitive example file you could provide in order to help assess the issue? Or perhaps some screenshots?
Do you have Pivot Tables in the file? Somet ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I have made an excel tracking document to track learners exams and assessments. I have set cell to turn colour depending on the percentage they achieved. What I would like to happen if the pass criteria is met in cells which is met i would like the cell t ... Read MoreHi Adam The issue is with merged cells and numbers in the cells between, you can sort it by the formula in the conditional formatting please see attached file
=SUM(COUNTI ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hello, I am trying "What if" tool in Excel for the first time. I have the following as input: I am getting the following output:
Please guide on what i got wrong in the inputs to have output of all "zeros"
Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Dear all,
I would be really grateful if someone can explain me how to use COUNTIFS function with duplicates.
Best, Abhishek Not sure what exactly do you mean, but you may check https://exceljet.net/formula/list-contains-duplicates https://exceljet.net/formula/flag-first-duplicate-in-a-list and ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I have a list of numerical inputs. The input number range from 1 to 8 I need to format the output to change the color of the number if it apperas in the input. How can this be achieved, with a formula or conditional formatting for a list of inputs that's ext ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I need to calculate daily totals based on the following <3,35 hours at 20 >=3,36 and <=4,45 hours at 40 >=4,46 hours at 60
Thanks for the help Antonio This formula that John Jairo Vergara Domínguez put together should work:
| |||||||||||||||||||||||||||||||||||||||||||||||||

I have about 16 Excel 2016 files that were created in a similar fashion. These were all working fine until about a month ago and now none of them will update correctly.
Each file has 2 or more worksheets. The first worksheet is formatted as a pretty "repo ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi All, I have two large excel sheets of projects information of almost identical data but sheet2 contains some incremental changes i.e. some new projects as well as some changes in the existing projects. Whats the best way to compare two sheets and get onl ... Read MoreThe best way to my knowledge is to use a tool called spreadsheet compare (I believe it comes with Office Professional Plus):
However, if you don't have that option I ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi Guys, So I was able to extract rows of data from 1 sheet with the following formula: =REPLACE(CELL("filename";A1);1;SEARCH("]";CELL("filename";A1));"") =COUNTIFS('L Costs'!I3:I30000;B1)+COUNTIFS('D Costs'!I2:I30000;B1) and an aggregate function My Inten Hi Fali,
In your sample formula returns #NUM!, the reason is it can't find no one cost center in the table. In your 56 sheet cost center picked-up from sheet name is t ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Okay so, being pretty knowledgeable in excel (I think) I am having a little trouble and im not sure if its just my brain is on overload or I may not know exactly what im doing please bear with me as I try to explain what i need to happen (In my head it so ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Not sure how to phrase this but ….
I have a worksheet that I want to track due dates based on a the date we received document. I used a formula to automatically calculate that due date but the cells are showing a date value of "02/01/00" as the cell the ... Read MoreHey lkm0707-
Just wanted to follow up with you and see if you were able to resolve your issue or if you still needed more assistance? Please feel free to post back to ... Read MoreMaybe try using a formula like this (See attached .xlsx file for additional reference):
If this doesn't help maybe you could provide further detail to your particular s ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hi,
I have a sheet with thousands of rows of data, with about 20 columns or so each. Does anyone know what functions I should/can use to search the sheet for duplicates across columns A and B? Essentially, I want to find all rows whose inputs for columns ... Read MoreHi @Zach Wurz
Refer screenshot, use this for finding duplicate values.
Before applying this option, Select the range like entire column (or) entire row (or) specific rang ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

Hello,
I am looking to format a spreadsheet so that any outliers are highlighted. These don't necessarily need to be mathematical outliers, I'd be fine doing something like "1.5x above or below average." The goal is simply to take note of anything out of ... Read More | |||||||||||||||||||||||||||||||||||||||||||||||||

I am using the INDEX, MATCH combination to find a value, but it returns #VALUE even when I press CTRL+SHIFT+ENTER. I can even open a workbook where the INDEX,MATCH formula has worked in the past, copy it to another cell and make sure the formulae are the ... Read MoreCan you please provide a non-sensitive example file that demonstrates your situation. Typically it's not necessary to use CTRL + SHIFT + ENTER in order to enter this typ ... Read More |

Latest Conversations

Top Liked Comments

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft