EzDevInfo.com

How do I hide the #DIV/0! error while a referenced cell is blank?

In Column C I have Production. In column D I have Goal. In Column E I have variance %. My formula is =(D11-C11)/D11

However, how do you hide the cells down the sheet until you put something in D11 & C11 to hide #DIV/0!. I have tried using the IF formula but seem to get it wrong?

Limit Excel CPU Usage

Is there a way to limit how much CPU usage excel has access to when running? I have a VBA script that calculates a bunch of giant array formulas. The entire calculation takes maybe 20 minutes using 100% of my CPU but I can't use my machine during the time and would rather have it run in the background at like 50% CPU usage so I can continue to do other things. Any suggestions?

My OS is Windows 7 Enterprise 64-bit and the Excel version is 2007-32 bit

AND function gives TRUE result for empty, BLANK cell - why?

I have blank B2 cell.

=ISBLANK(B2) gives TRUE

A simple logical test on it returns FALSE

=IF(B2,TRUE,FALSE) gives FALSE

Yet when directly used in an AND statement, it returns TRUE

=AND(B2,TRUE) gives TRUE

Of course, when indirectly used in an AND statment, it still returns FALSE

=AND(IF(B2,TRUE,FALSE),TRUE) gives FALSE

Could you explain me please why my Excel behave in that way?

Excel formula to convert boolean values into {0, 1}

What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}?

Supposing there is one shorter than =IF(cond,1,0).

How to count number of distinct values in a range?

I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.

There's no function =COUNTDISTINCT(A2:A100) so what can I do instead? (Excel 2003)

I can't exactly use answers to this similar question because I don't want to modify the table or the filtering. I need an addition in the worksheet, not a modification.

Can you do Regular Expressions in Excel without VBScript?

I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to the spreadsheet for this because I can hard-code a solution faster. It's just that the fastest solution would be a function. I can't find one, though. So maybe there's nothing.

Anyone know?

Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP from another workseet or workbook.

I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?

Any thoughts or ideas on this or another strategy?

Excel function that evaluates a string as if it were a formula?

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?

This would be helpful for another project where I would like to be able to write 'dynamic' formulas in Excel.

In Excel how can I sum all the numbers above the current cell?

I want to have a column in Excel that consists of a header, a bunch of numbers, and then have the sum of those numbers at the bottom. I'd like the sum to adapt to the insertion of new numbers above the total. Something like this:

Numbers
 1
 2
 5
10

18        Total

If I later insert 10 new numbers in the middle of the list, I want the sum to automatically include them.

I know the SUM() function can sum a whole column, but if the total is also in that column then it complains about a circular reference. How can I just sum the numbers above the total?

How to get the current column name in Excel?

What is the function to get the current line number and the current column name for a cell in Excel?

Excel: Replace occurences of one character with another in a String?

I want an excel formula that will change Haverford, PA to haverford,+pa. (Text is to lowercase, and spaces are replaced with plus characters.)

The following will get the first space, but not all of them:

=REPLACE(E19, SEARCH(" ", E19),1,"+")

What is the Excel hotkey to re-calculate all formula in sheet?

I have a sheet that contains many formulas. But they do not recalculate automatically when I change input.

I'm looking for a hotkey, that can re-calculate the sheet. According to this page, F9 calculates all sheets in all open workbooks, and Shift + F9 calculates the active sheet. Neither works for me. I know a tip: delete any row to refresh sheet. But my file is too long, and I'm not comfortable with this method.

Type math formulas in Microsoft Word the LaTeX way?

I wonder if there is some free solutions for typing math formulas the LaTeX way in Microsoft Word document (Office 2007)?

How do I get the day name into a cell in Excel?

Given a date, how do I get the day of the week (e.g. "Monday") into a cell in Excel?

How to compare two columns and find differences in excel

I have two columns in Excel that I want to compare and find the differences between them.

Suppose:

• Col A has 50 numbers i.e 0511234567
• Col B has 100 numbers in the same format