functions

Hi I know how and when to use substitute() and replace() function. But my question, I found that Replace tool is doing the same. Is there any case that Replace tool is not going to help then I will have to use substitute() or replace() functions. Thank you very much.

Hi, I'm using Excel 2016 64bit on Win10. I have a 195Mb workbook, 7 sheets. Set to manual recalc and the book calcs in 2-3 seconds which is fine. My issue is that enter entering a value in one of the 50 or so input cells (all on sheet1), excel is typically unresponsive for up to 20 seconds...

Using countif doesn't work because formula in CF (= i don't wanna enter conditional formatting always) is too long with many other functions and there are 4 cells. Is there any other way to get results of formulas in CF outside CF? Thank you.

Hello, Does MrExcel have a place to view useful formulas that people have shared over the years? If not, does anyone know of a good source for those types of formulas (hopefully categorized for easier/quicker finding)? I'm not referring to a list of Excel functions (which are readily available...

Hi, In column range A-Z, row 1, there are cell values in E1, M1, Y1, the rest are blank. What functions can I use to determine the far most right column containing any value, in this case Y?

I wrote custom VBA functions in Excel that stop continuously calculating when I activate a different workbook. They are populated with RTD streaming from ThinkorSwim and generate audible alerts when certain conditions are met. They work great until I open/activate another workbook and then...

URGENT: I am having a problem with the below formula and seem to be having an off day and can't figure this out. The formula will match the first criteria but not the second and 3rd IF functions. It will just return "False" for the other 2. Any idea what I am missing...

Hi, Please HELP! I am trying to create a 2nd tab where it looks for text "OFF" in ColumnB from ColumnA, and copy "Name" in ColumnA to 2nd tab. At the same time, the function skips blank cell. TAB 1 (ColumnA) (ColumnB) <tbody> Name Amount Anne OFF Amy Mike Doe OFF Anne...

Hi, I have an excel sheet with a whole heap of functions in each column, and when I insert a new column I'd like to have the functions automatically populate the new column without having to drag the rows into it individually. Is this possible? Thanks :)

I built a fairly large and complicated financial model for valuation purpose, I’m now concerned with calculation speed. A couple of questions: Does table slows down calculation a lot? My data is kept in tables so that other formula referring to the data will auto update when adding deleting...

When I protect/unprotect cells and then protect the worksheet I allow users to Sort / AutoFilter / Insert Rows. They are only able to perform these functions once and then that ability is removed and they can no longer perform those functions. Is there a way to override this and always allow...

Hi, I am currently using excel 2016 and I am trying to create a formula that can look up a specific table name and return the entire data table, but I am not sure how I would go about doing this. I have tried using the lookup functions but that wont return the entire table. If anyone has any...

Team: The last parameter in VLOOKUP() essentially tells EXCEL whether the range being used for lookup is in ASCII order or not. If not, it has to plow through all the entries in the range, as opposed to doing a binary "search all" (of COBOL fame). So not understanding this for a long time, most...

HI5 folks, I'd like to know how to calculate the sum of one function that has several values from 1,2,3 .... to n the function is z = (((1 + w) ^ 2))l = (((p) ^ (n - 1))) y = ((z * l)) ^ ((n) / 2) I'd like to know how to calculate the sum ∑ of y, for example from 1,2,3 .... to 10 when...

Hi All! I am trying to put this worksheet function =IFERROR(VLOOKUP(SUBSTITUTE(E2,"/","-"),Master!$E:$E,1,FALSE),VLOOKUP(E2*1,Master!$E:$E,1,FALSE)) Into some VBA code that just drops the formula into a cell and copies it down the length of the data set. For some reason it is throwing me a...

Good afternoon - since our upgrade to Win 10/Office 2016 64 bit, a lot of my functions, including those to extract IP addresses from HostNames and vice versa no longer work (no surprise there, then) Even when I add PtrSafe to my existing functions that worked in Win7/Office 2010 32 bit, the...

First: Thank you for reading. My issue, I have a formula on a waterfall type report that uses a lot of countif functions to determine if a site is forecasted/actualized, and counts it if it is. I bring in data to the workbook through a series of queries, and unfortunately, the client changes...

Hi I wanted to use Trim function (Application.Trim) in my code. So I went to Object Browser and typed Application, thinking that under Application, I will see these functions. Otherwise why it is (Application.trim). But to my surprised, I could not find Trim or other functions under...

Hi, I'm having a bit of trouble writing a formula to help find the nearest match. My search up value is 185. I would like to write a formula that will read the list of numbers below, and provide the nearest approximate match. I have tried using both VLOOKUP and INDEX functions but I cannot...

Hey Group I need some Help to this formula =IF(And(D9=1;L11=1);D12) That Part Works Fine i now need a "Or function after that Working formula so it might look like that perhaps =IF(And(D9=1;L11=1);D12)or If(And(D9=1;L11=2);D11) i have try that but it wont Work and not for any thing i...