formulae

Hi All, i have a left formulae which copies the 2 first letters from another cell. I have put a true /false statement in this cell though now, and now the left formulae is showing TR and FA before data is entered. Is there a way for the Left formulae to ignore if data in the cell is TR or FA...

Hi, I have a number of worksheets in a workbook from which I extract data using lookup-type formulae to a single “Data” worksheet in the same workbook. The data in the “Data” worksheet is arranged in a conventional form, with column A containing Product Name, column B showing Sales, column C...

Good day all I know there is someone that can assist. I work at a company who's accounting system always exports negative numbers/credits with the annotation of cr at the end of the number for eg, 1000cr, instead of -1000. Is there a formulae that one can use to change it to the latter?

Hi Guys, I have an export which gives me holiday start and end dates e.g. 01/08/2018 - 20/08/2018. I need a formula which will enable me to look at all the dates in the range and enter the value 1 in each corresponding date column on my holiday chart. Can anyone recommend a good formulae for...

Hi, I have sample data in columns A and B, with product names in column A and sales per product in column B. The last row in the range has the world "Total" in column A and the total of the sales values above in column B. In E2:F4 I would like to create formulae to extract from A:B the...

I've got a big forecast model, which takes about 5 secs to calculate. There's a lot of formulae that do SUMIFS with 4-5 parameters and I want to make it more visible what it's doing so other people can use it. is there much difference in referencing the ranges directly, or using dynamic...

Hi! I'm having a problem, probably a silly one but it's important for me and i can't figure it out. In my excel sheet i'm putting a countif with the range being a whole column, the problem is that in that same colum is also located the countif formulae. That is causing it to stop counting...

I have two spreadsheets stored in the same folder on OneDrive. Values in Tab1 on Filename1 is referenced by Filename2. Filename2 contains formulae like: =IF(ISBLANK('[Filename1.xlsx]Tab1'!A1),"",('[Filename1.xlsx]Tab1'!A1)) and this produces the desired result. However, when I close and reopen...

HI Guys PLease tell me how can add , after date 10/26/2017 2:17:00 PM but when i use & "," formulae it convert date into <tbody> 43034.5951388889, and i am unable to convert back into required format </tbody>

Hi all, I have a spread sheet where I record different departments response times to requests for information. I am looking for a formulae which will return an average (mean) for each department, so far so simple just simply using this formulae =AVERAGEIF(F$2:F$700,"Corporate",L$2:L$700) which...

I have the following array formulae repeated about 2.2 million times. Any other way to have it written to increase calculation time? =IF(E5:BB5="","",RANK(E5:BB5,$E5:$BB5,1)+COUNTIFS($E5:$BB5,$E5:$BB5,$BD$3:$DA$3,"<"&$BD$3:$DA$3)) Thank you!

Hi, I have tabular data in A5:H11 and in rows 1:3 there are column heading references that are used in formulae in the table below to extract data from other worksheets in the workbook. I would like to move the lookup references out of rows 1:3 so that my table is a single range with no...

Hi, Let's say I have 10 in cell A1 and 11 in B1. If I put in the formulae "=A1<B1" into cell C1, I'll get a TRUE! I want to be able to make the symbol "<" a parameter such that I can put say (in C1): "=A1indirect(D1)B1" with the "<" inputted in D1 to still be able to maintain the formulae...

I want to hide the formulae i used in the spreadsheet whats the process secondly i want that the duplicate values to be deleted row wise how. Thirdly i want that the 5-5=0 its 0 comming but this should show as - how to bring that

Hi, I have a requirement to restrict all the following special characters from being entered into a cell: \/:%'*?<>|". Have tried a few formulae but none worked in restricting all of them

Hello! I had posted this question on Stack Overflow, but was told that it isn't the right place for such a question. I have two sheets in Excel, one with formulae calculating business costs, and another where I'm trying to calculate revenue projections based on those costs. Images: Costs...

Hello Team, I need an up and down arrow sign which should show the result. If it is above that it should give green, if it is below than red. Below is the sample data. Please fill the formulae in the empty box. <tbody> Sign <tbody> Hours Required/Day </tbody> <tbody> 774:33:32...

Hi all - wondering if someone would be able to assist me. I have a workbook with formulae in multiple sheets referring to other sheets in the same workbook that are quite basic (SUMIFS, COUNTIFS, IF statements, etc) - we'll call this workbook A. There are dynamic ranges and PivotTables as well...

I have two columns of text data. I want to combine them (For example, one column with first name, second column with last name; I want one column with first and last name.) I have searched for the last couple days around the web and can't find any way that will work. I don't want formulae. I...

I'm sure this has been addressed before, but my Google Fu fails me. Sometimes I'll be merrily copying formulae around and notice that some of them return N/A or #DIV/0. I can revise the cells so that the formulae include =if(isna or if(iserror(, as appropriate, but sometimes it's not practical...