## 36 Q & A posts tagged with function

### What Kind Of Excel Function Do I Need?

Q: In cell B6, insert a function to calculate the total number of units in the apartment complex in A6. Be sure to enter the criteria range as an absolute reference. Copy the function down through B10.

In column A there is a list of different types of apartments and I need to find total units of each apartment column B. what kind of function would I use to count and recognize each number of units for each different apartment?

### Answer

I am not sure what do you have exactly in A6, however, I am assuming only

paste this in B6

=COUNTIF( A:A, A6)

Then fill down

### What function to use in excel?

I have a range of the first five digits of telephone numbers and I need to replace them into country name. so i need to compare very single digit of these telephone numbers with the range of prefixes i have before deciding the country name. is there any function to do that ?

I used lookup function but it compare the number exactly

example if i have the number 27800 and in my prefixes i only have 27 it will not recognize it.

### Answer

You can also still using VLOOKUP but with some modifications

Modify your list of prefixes to have the entire number with zeros

so 27 becomes 27000, 15 to become 15000, etc

here you can use the VLOOKUP with Range lookup to be TRUE, like this

=VLOOKUP( A1, D:L, 2, TRUE)

The "TRUE" will let you search for the nearest item to the searched one before it

Assuming your list of prefixes is sorted in ascendant order by prefix number

Excel help has more details about the "TRUE" in VLOOKUP

### What function to use in excel?

I have a range of the first five digits of telephone numbers and I need to categorize them into country name. so i need to compare very single digit of the telephone numbers before deciding the country name. is there any function to do that ?

### Answer

Use VLOOKUP with TRUE in "range lookup" as in this question

http://answers.yahoo.com/question/index?…

You can also use other suggestions there

### Is there a way to change the auto-assigned value of empty cells in excel?

In excel, empty cells are treated as though their numeric value was 0, and as though they had no text value.

I'm using a custom function that runs through a range of data (which contains 0 values) and gives me a result based on the range's findings.

Currently, there are no blank cells before the end the range. Therefore, I can use CountA(range) to tell my function how many times to iterate (i.e. to continue until it reaches the end of the range).

I wish to instead specify to the program that it must continue to the end of the range (another problem) and ignore blank spaces.

Rather than re-writing all of my functions to ignore these empty cells, is there an excel option that will allow me to force excel to treat empty cells as a value other than 0?

### Answer

If I got you correctly you can use the formula

ISBLANK()

When you put it inside IF, you will will get if the cell is blank

CountA will not count blank cells either

let me know if that solves it or not

### Is there a way to send batch emails from a list in Excel through Outlook at different times?

I am curious to know if there is such code or function that can send batch emails from a list in Excel with a personalized message through Outlook for delivery at different times.

### Answer

This is the file that does that

maybe except for the "delivery time" thing

http://www.vbaxlman.com/Files/?ID=4S374y…

You can customized it if you like

VBAXLMan

### How can I insert intervals of numbers, not discreet elements into Excel?

I need to sketch the sin, cos functions' graph in Excel. I have a problem with a domain. I don't want to manually type myriad values - even then, the graph is sharp, not curved, and inaccurate. Is there a possibility that I simply set an interval, infinite, but bounded set, as a domain? If yes, how do you do it?

### Answer

You need as many points as you can to do the exact curved sin

to answer your question, do the following:

- Do the first number you want in a cell, say you do 5 in cell E4

- Now do the second number with the intervals into the second say.

So if you want to have the numbers as 5, 15, 25 (with 10 interval), put 15 in cell E5

- Now select both cells

- Drag the selection from the black square at the right bottom corner down to whatever range you want.

Or, you can also do:

- Put 5 in cell E4

- Select some range of cells including cell E4 (Cells E4 to E20)

- Go to:

Edit > Series > Fill (if you have Excel2003 or earlier)

Home > Fill > Series (below SUM sign)

And select the interval and stop value

VBAXLMan

### I need to have the auto sum function in excel calculate alphabet characters?

I would like to have the sum function include alphabet when calculating sums. How can I do this?

### Answer

Did you try the formula LEN

LEN is the formula to sum how many characters in a cell

So, if you add this in column D (Assuming the column C has the cells you want to sum its chars). paste this

=LEN(C1)

Then copy it down to get the length of all cells characters

Then do a regular SUM on column D

Hope this will help

VBAXLMan

### Why does it come up with "#VALUE!" when i subtract 2 cells in excel? and how do i correct it?

How do i get it, to come up with the actual answer. Thank you! :)

### Answer

Another way is using the N function

Like this

=N(A1)-N(A2)

N will convert the text (that represent a number) into a number

VBAXLMan

### How do you write a VBA function to work in any Excel spreadsheet you have open?

I wrote a function and saved it in my personal.xls file. When I want to call that function from another spreadsheet, I have to write Personal.xls!FunctionName(). Is there a way to write the code so that I don't have to reference my personal file each time?

### Answer

That is because your function name is the same as a build-in function already used in Excel

Try a unique function name, like MYFunc0001

That is the only reason I see, otherwise, the function would be work easily as long as the function file is open

I am using that all the time

let me know

VBAXLMan

### How do I remove blank (but not necessarily empty) cells from a drop down menu in excel?

I want to have a row of drop down lists. In each drop down I want all the options to exist EXCEPT any that have already been selected. This is my primary problem. The closest I can get is to remove value from the drop down range by an IF function in the source data, so that the list updates based on what has already been selected. This requires extensive functions and also leaves blanks in my drop down menus. I'm keen to have the drop downs only contain the remaining options and no blank gaps. Are there any solutions to either the primary problem, or, failing that, a solution to remove the blank (but not empty) cells from my drop down menus?

Cheers :)

### Answer

You have very interesting request

Check out this file

http://www.File1.net/Links/NewList.xls

It has new list populated every time user selects an item, the new list will have the same as old list without the newly selected one, it was done for tutoring purposes

It should do what you are looking for, you might need some tweaks though

That was done using the following Excel features:

Data Validation

OFFSET function

IF function

Name

Let me know if it helped or not

VBAXLMan

### How do you add an npv function into vba for cash flows?

I have interest rate as 10% and future value of 1,000. I've already done everything in VBA that allows excel to show 10 years of present values, future values and compounding factors. I just don't know how to add a function in for NPV so that a specific cell would add up all the present values that were already calculated with the formula pv = fv / (1+irate) ^ 1 using VBA and not excel's npv function or sum of values.

### Answer

You have several options here

1- Use the mathematical way to calculate the NPV

2- Use some temporary cells to set the values and use NPV in another cell do calculation, then read the result

3- And the one I used a lot is to use WorkSheetFunction object to use Excel function in VBA, like this

NewValue = Worksheetfunction.NPV( MyRate, MyAmount, etc)

If none of those helped, that means I didn't get your question right, reply me here.

VBAXLMan

### Microsoft excel function?

how would i enter f(x) = 33.5 +1.3log(x+3) in microsoft excel as a function in order to graph it?

### Answer

Let's say you will have your x's value in cell A2, paste this in B2

=33.5+(1.3*LOG( A2+3))

And press Enter

If you got new X value in cell A3, copy and paste cell B2 to B3, and so on

VBAXLMan

### How do I populate corresponding cells with a code in excel?

Hello Everyone,

I have been struggling this for so long , your help will be much appreciated.

I am trying to make an invoice for a sales order with many different items. I need to know how I can populate the corresponding cells automatically from data stored somewhere else when I enter the product code for the specific item so I don't have to always enter it manually. For example, if I enter product code A-1 on cell A, I want the other cells on the line to be filled out automatically like "Hair Styling Iron" on cell B and "$50" on cell C and so on...

Thanks!

### Answer

Once you have this table in some sheet (say Sheet1)

A, B, C, D

ID, Name, Description, Price

ID1, Name1, Hair Styling Iron, 50

Then in your invoice sheet, let me assume you have cell B15 has the Item ID that you want to populate its values, then in cell C15, paste this

=VLOOKUP( $B15, Sheet1!$A:$D, 2, FALSE)

And paste it down to fill other items

Then in D15, paste this

=VLOOKUP( $B15, Sheet1!$A:$D, 3, FALSE)

And paste it down to fill other items

=VLOOKUP( $B15, Sheet1!$A:$D, 4, FALSE)

And paste it down to fill other items

And you got what you are looking for

Let me know if that make sense or not

VBAXLMan

### How do you run a macro within a formula in microsoft excel 2007?

.... for example If(A2=A3, "Run Macro", "")

thats what i want to do, i just dont know how to do it

### Answer

A formula is ACTUALLY a macro

It is FUNCTION macro and not SUB macro

So, to do that, you need to replace the lines of your macro

from

Sub WhatEverName()

....

End Sub

TO

Function WhatEverName()

...

End Function

AND, putting that function in a module (and not a form module) will enables you to run it as formula

However, you need to go to "User-Defined Functions" in the Insert Function dialog and you should see it there

Keep in mind that this is a macro, means that you need to have the macros enabled for that file to make it run

Also, you can run that formula from any opened file, as long as the file that has the function (the function macro) is open

You can see it in action in this free file

http://www.file1.net/project.asp?id=b2vi…

Does that make sense? let me know if not

VBAXLMan http://www.file1.net/project.asp?id=b2vi…

### In Microsoft Excel, how does protecting a worksheet affect the autofilter function?

### Answer

In the Protect Sheet dialog, you can select what the user can do

You can select any of the checkboxes in the List including

1- Sort a table

2- Filter table (Show/Hide rows)

3- Select cells

VBAXLMan

### How to calculate expected return and correlation coefficients in excel?

Hi everybody. Someone have any idea how to calculate and what excel formula i have to use to find the expected return and correlation coefficients if I have the following data:

Date Open High Low Close Avg Vol Adj Close*

1 868 872 827 866 7083169900 866

2 855 876 836 870 6839301900 870

3 840 857 815 857 6226187600 857

4 809 846 780 843 6286869900 843

5 772 833 772 816 6952819900 816

### Answer

The CORREL function will give you the correlation coefficients, like this

=CORREL( A1:A5,B1:B5)

To find it between the two arrays A1:A5 and B1:B5

VBAXLMan

### Excel: is there a way to emulate the lower function without using lower()?

Excel: is there a way to emulate the lower function without using lower()??

note: it has to work for cells with 2 or more words

### Answer

I think there is, but it is going to be too long and time consuming

If your cell is in A1, paste this in B1

=IF(CODE(MID( $A$1,COLUMN()-1,1))=32," ",CHAR( CODE(MID( $A$1,COLUMN( )-1,1))-32))

Then drag it to right to cover all letters of that text

After that, you can easily use CONCATENATE or the & symbol to assemble them into one cell text, like this

=B1&C1&D1&D1

or

=CONCATENATE(B1,C1,D1, E1)

until you reach the end

good luck

VBAXLMan

### Code in Sheets vs. Code in Modules; Excel?

I transferred all my module code into specific worksheets modifying Subs to Private Subs and replacing Form Controls with ActiveX controls. Am I running into any disadvantages doing so? Is it helping me any, besides the fact that pressing ActiveX buttons feel more like clicking buttons than Form Control buttons do?

### Answer

I can think of one...

If you want to do a USER-DEFINED-FUNCTION, then you can not do it in a worksheet module, you would have to do it in a Module

Other than this, You still can call any sub/function in a worksheet form other worksheets, using the "Public" key before the sub/function name

One more thing, selecting ActiveX or Form controls are not affected by Module or sheet module

VBAXLMan

### I want to add no. of hours spreaded in multiple rows in excel, can any body tell me the formula for the same?

### Answer

If you have these cells values entered as numbers (and not dates), then use the regular sum function, like this

=SUM(A1:A5)

If you have these cells values as times (or dates) then that would requires some work

Try using this one

=HOUR(SUM( B:B)-INT(SUM( B:B)))

Where you have the dates in column B

VBAXLMan

### I want to convert number to text using Excel 7.0 function.please give suggestions?

Ranjeet chiplunkar

email ranjeet_chiplunkar@yahoo.co.in

### Answer

Here is the file that does that

http://www.file1.net/project.asp?id=KoFR…

VBAXLMan

### How do I write an excel formula to display an asnswer if a Number in one cell appears in another column?

I need to find out a formula that will display an answer in a blank Cell if the number contained in another cell in the same row is present within a whole column on another worksheet.

For example, if 1 is present in cell B1 in worksheet 1, then I want the formula to be able to search the whole of column C in worksheet 2 for that number (1) and if it is present display a custom text (e.g. training) in the blank cell A1 on worksheet 1.

I know my description is pretty garbled, but if someone could help I’d be eternally grateful.

### Answer

I guess you want to know the famous VLOOKUP function

Here is a free video lesson that talks about that

http://www.file1.net/lesson.asp?id=b1vi8…

Check out other lessons below

VBAXLMan

### What does the $ do in an excel spreadsheet?

What is the function of the $ sign in an excel spreadsheet?

### Answer

It is called the Absolute and Relation symbol

Here is a free lesson about what it is doing

http://file1.net/lesson.asp?id=GlcNf6q11…

VBAXLMan

### Is it posible to change the colour of a cell by using functions in MS-Excel ?

By Using functions in Ms Excel can we change the colour in a cell.

for example: if A1=1 then RED colour, A2=2, Then YELLOW colour..

### Answer

In Excel2003, go to

- Select the cell

- Format > Conditional formatting

- Enter the values and the format for each condition (maximum 3 conditions)

- Press Ok

In Excel2007

- Home > Conditional Formatting > Highlight cells rules > More rules

- Select the values and the format for each

- You can do up to 256 conditions

Enjoy it

VBAXLMan

### How do I merge multiple Excel worksheets into one Master sheet that updates?

I am creating a program for the agents in my real estate office. It is a Lead Management program. It has 5 worksheets, one for each different type of customer. I would like a 6th sheet that will contain all the names from the other 5 sheets. I also would like the "Master" sheet to update automatically when a new lead is added or changed. HELP!!!

### Answer

Actually

SUM function WILL work fine

It will omit cells with names, or anything else than numbers

So it will sum only the numbers in the range

I would suggest checking out this video lesson, it will help you to do SUM across sheets

http://file1.net/lesson.asp?id=fjame5p10…

Then do the SUM using the same context

=SUM( INDIRECT( bla bla bla))

Lets assume you have the sheet name in cell A1, and you want to sum the row 1 from that sheet.

So paste this in cell B1

=SUM( INDIRECT( A1&"!"&ROW()&":"&ROW() )

Then copy it down to cell B2, B3 and B4 to make these cells get the total of the corresponding rows from the sheets in the range A1, A2, A3 and A4 respectively

Hope this helped

If not, mail me here

VBAXLMan is here to fill your Excel needs

### Filtering data in Excel 2007 based on formatting?

I have two columns I need to filter. Some have text and some have text with strike-through (a line going through the text). Does anyone know how I can filter based on the strike-through - or if that's not an option can I filter based on anything that is not formatted like the regular text so it will separate the cells with the strike-through? Thank you.

### Answer

There have been a lot of requests to read format through formulas

Since it is nit supported yet, here is a user definec function to do that

Function IsStrickeThrough(CellAddress As Range) As Boolean

If CellAddress.Rows.Count = 1 And CellAddress.Columns.Count = 1 Then

IsStrickeThrough = CellAddress.Font.Strikethrough

End If

End Function

To use it...

- Press ALT + F11

- Insert > Module

- Paste this

- Now go to any cell and use it like this

=IsStrickeThrough( A1)

Assuming the cell you want to check is A1

Enjoy it

VBAXLMan

### Is There A Function On Microsoft Excel Which Allows Me To Highlight Duplicate Records?

What I need to do, is to paste 2 different corporate structures into one spreadhseet and then delete the duplicates. Is there a function on microsoft excel which ahighlights any duplicate records in column A of excel?

Thanks

### Answer

Yes

If you are using Excel2007, the Conditional Formatting in the Home tab has duplicates, go to

Home > Conditional Formatting > Highlight cell rules > Duplicate values

If you are in Excel2003, then you may need to use a function plus the conditional formatting like this

Use the COUNTIF function in an empty column to show 1 if duplicated, 0 if not like this

=IF( COUNTIF( A:A, A1)>0,1,0)

Then apply conditional formatting to that column to color if the cell value equals 1

Enjoy it

VBAXLMan is here to fill your Excel needs

### Excel Chart Function?

The problem is this: To generate chart on a chart sheet u should:

1. Generate it on excel sheet 2) Select the function Move Chart 3) select Chart.

In my case, when I select Chart (3) I get a blank sheet and the chart does not appear on the selected chart

Thank u

### Answer

That sounds weird

Try this...

You need to do these steps as they are the most popular reasons for problems like yours

1- Clear history and cookies

2- Delete temporary internet files

Both 1 and 2 are in Internet options from Control panel

3- Delete "Temp" folder content (and not the folder), it is in your C: > documents and settings > YOURUSERNAME > Local settings > Temp, this will solve a lot of problems

4- Restart computer and try again

Do Step 3 if you are using WinXP, if you have VISTA, then step 3 would be

3- Delete "Temp" folder content (and not the folder), it is in your C: > Users > YOURUSERNAME > AppData > Temp, this will solve a lot of problems

VBAXLMan

### Not Show Excel Formula Results?

hi

I have a formula with IF function in a cell and I dragged it to replicate the formula .

This added FALSE in all the cells how do i avoid that and have nothign displayed but the formula will still be active in that cell

thanks

### Answer

Use this mask

=IF( [condition] , "", [result])

Something like this

=IF(A1-"", "", 40/A1)

This will show nothing if the cell A1 is empty

You can also try this

=IF( NOT(ISNUMBER( A1)),"",40/A1)

Enjoy it

VBAXLMan

### How to solve this problem with Excel conditional formatting?

I have a table on an Excel spreadsheet, which table contains only numbers. I want to color a "path" in this table using the following algorithm:

1) Make current the upper left cell and color it.

2) Examine the cell to the right of the current one and the cell beneath the current one. Make current the one that contains the smaller of the two numbers and color it. If the two numbers are equal, make current and color the cell beneath the current one.

3) Repeat 2) until the bottom or the right edge of the table is reached, whichever occurs first.

Can this be done using only the means of Excel's conditional formatting (i.e., no VBA) and how? That is, what formulas for conditional formatting to use?

### Answer

Conditional formatting will "FORMAT" the cell and not change the cell content

To change cell content (or value) you will need a function

Based on your request, IF function would do the trick for you

Now, if your first cell is A1, then paste this into it

=IF( OR(A2="",B1=""),"", A2<B1, A1, IF( A2>B1, B1, A2))

Then fill cell A1 down

Now, Since the Conditional Formatting is to "FORMAT" cells, I don't see where you need it here

let me know

VBAXLMan

### How to create a function in Excel...a second question?

Thank you to the person that helped me yesterday...a huge help! The isanswer and match functions worked great!

I now need to write a formula to do the following: I need to find if the value in cell C1/Sheet 1 is in column D/Sheet 2. If true, return the value (text) in Column E/Sheet 2 that is in the same row as the value found in Column D. If false, return "null" or "false".

I tried the same isanswer and match formula as it seemed it was close to the solution but it didn't work. Can you help me again? :)

### Answer

Paste this in cell E1 Sheet2

=IF( ISNA(MATCH( Sheet1!C1, Sheet2!D:D,0)), "","Text")

And fill it down

----- Edit ----

Yes, in this case, use this

=IF( ISNA(VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE )),"Not found",VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE))

This whould work

VBAXLMan

### Excel 2003 - VB - Accessing abilities between modules for public functions?

I have one module with:

"Public Function Code128B(inpara As String) As String"

and then the function (i'm pretty sure it's copyrighted etc; it said Shareware on a few sites i was at and it's for barcodes)

I have another module with:

----------

...

Dim foot As String

foot = ActiveWorkbook.Name + " " + ActiveSheet.Name

ActiveSheet.PageSetup.CenterFooter = "&""MRV Code128bMA,Regular""&14 " & Code128B(foot)

...

----------

And the error is returning is that it is an "Ambiguous Code Name"

Is there something fishy going between modules with public functions? I'd think that a public function means it's always available to every module but I'm not sure.

If this is not enough data to solve the problem say so and I'll just let the whole thing drop (it's not my code or job to fix it :P )

### Answer

You are right

"Public" will set this function public to every other module inside that workbook

And I quote "That Workbook"

If you are running this from another workbook, you need to call the function in another way

Workbook1.xls!Code128B(Foot)

If you are using the same workbook, then you may need to check two stuff:

1- You have a reference to the library that is used by that function

Some function requires you to add a reference (Toos > References) to a specified library to make it rub

2- You don't have the "Option Explicit" in any of your modules

If you still face the problem again, mail me some sample files, I can do it for you (May be for free)

VBAXLMan is here to feed your Excel needs

### Searching across sheets in Excel?

How can I do a search for several items across sheets in Excel, whether using vlookup or any other function

### Answer

This is the lesson for that

http://www.file1.net/lesson.asp?id=28

Enjoy it

VBAXLMan is here to feed your Excel needs

### Can i back calculate a formula using excel?

I have a polynomial function here that i known of the y values and want to derive the corresponding x values. Anyone knows how to do it??

y=-0.0969X^4+342.16X^3-452972X^2+3*10^…

### Answer

Your formula is cut

Try to insert spaces between it, Yahoo! Answers cut the rest of it

VBAXLMan

### How do you conditional format a hyperlink in excel?

trying to hide links where data doesnt exist yet with white font

### Answer

You will need to use HYPERLINK function, (Not Insert Hyperlink)

along with IF function.

It is somthing like that

=IF( A1=1, "", HYPERLINK( "destination"))

So when cell A1 equals 1, then this cell will show nothing, but when A1 equals anything but 1, this cell will show the hyperlink you specify

Enjoy my profile, I am the VBAXLMan

### I have data in a column in an Excel spreadsheet and I need to use it in formulae in a row on another sheet.?

I don't want to have to type the formula over and over for each cell! Is this possible? Using the '$' sign doesn't work...

### Answer

You can use

1- The Copy + Paste Special (Transpose)

2- Use the COLUMN(), ADDRESS functions like this:

Your source column is in D in Sheet1, you want it in row 5 in sheet2, so in cell A5 in sheet2 paste this

=INDIRECT( "Sheet1!D"& COLUMN())

the function in cell A5 will call the value in cell D1 in sheet1 and so on.

You can change the sheet name of the source column by changing Sheet1 in the formula.

If you are not starting from D1, or you want the row to start from other than A5 then add the -1, +1 after the COLUMN() to modify it

3- Use the TRANSPOSE function

I think this will answer you

Enjoy my profile, I am the VBAXLMan

### This is regarding Concatenate function in Excel:?

I am trying to concatenate a Currency value ( $ 233,456,389) with a

percentage ( 74 %) in Excel using this function :

=CONCATENATE("$ ", TRUNC(B313)," @ ",TRUNC(C314)," % ")

I would like to have it as $ 233,456,389 @ 74%

Instread I am getting $ 233456389 @ 74% (currency value without commas)

Can someone help me in getting the commas in the currency display ?

### Answer

Add this to the TRUNC function

TEXT(B313 ,"###,###")

Means the TRUNC would be

TRUNC(TEXT( B313, "###,###"))

and your final function will be

=CONCATENATE("$ ", TRUNC(TEXT( B313, "###,###"))," @ ",TRUNC(C314)," % ")

Enjoy my profile, I am the VBAXLMan