ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

36 Q & A posts tagged with function

Nov 19

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?


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

Then fill down

Saturday 11/19/2011 8:42:40 PM
Nov 19

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.


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


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

Saturday 11/19/2011 11:42:40 AM
Nov 19

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 ?


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

You can also use other suggestions there

Saturday 11/19/2011 9:29:16 AM
Nov 10

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?


If I got you correctly you can use the formula
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

Thursday 11/10/2011 3:34:45 AM
May 15

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.


This is the file that does that
maybe except for the "delivery time" thing…

You can customized it if you like


Saturday 5/15/2010 12:03:22 AM
May 7

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?


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


Friday 5/7/2010 1:54:43 PM
May 2

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?


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
Then copy it down to get the length of all cells characters

Then do a regular SUM on column D

Hope this will help


Sunday 5/2/2010 6:53:36 AM
May 1

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! :)


Another way is using the N function
Like this


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


Saturday 5/1/2010 2:11:18 AM
Apr 13

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?


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


Tuesday 4/13/2010 7:47:09 AM
Apr 12

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 :)


You have very interesting request

Check out this file

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

Let me know if it helped or not


Monday 4/12/2010 2:35:47 AM
Apr 11

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.


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.


Sunday 4/11/2010 4:36:44 PM
Apr 10

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?


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


Saturday 4/10/2010 10:08:03 PM
Apr 9

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...



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


Friday 4/9/2010 9:02:55 AM
Apr 5

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


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
Sub WhatEverName()
End Sub


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…

Does that make sense? let me know if not


Monday 4/5/2010 4:33:00 PM
Apr 26

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


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


Sunday 4/26/2009 4:36:54 PM
Apr 26

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


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


Sunday 4/26/2009 5:38:15 AM
Apr 26

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


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

until you reach the end

good luck


Sunday 4/26/2009 3:23:34 AM
Apr 25

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?


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


Saturday 4/25/2009 1:48:16 PM
Apr 25

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


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

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

Where you have the dates in column B


Saturday 4/25/2009 8:50:20 AM
Apr 23

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

Ranjeet chiplunkar


Here is the file that does that…


Thursday 4/23/2009 4:18:21 PM
Apr 22

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.


I guess you want to know the famous VLOOKUP function

Here is a free video lesson that talks about that…

Check out other lessons below


Wednesday 4/22/2009 2:26:46 AM
Apr 17

What does the $ do in an excel spreadsheet?

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


It is called the Absolute and Relation symbol
Here is a free lesson about what it is doing…


Friday 4/17/2009 9:44:03 PM
Apr 13

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..


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


Monday 4/13/2009 3:04:36 AM
Apr 9

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!!!


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…

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

Thursday 4/9/2009 2:01:12 PM
Apr 7

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.


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


Tuesday 4/7/2009 9:01:21 AM
Apr 7

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?



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

Tuesday 4/7/2009 5:22:21 AM
Apr 6

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


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


Monday 4/6/2009 2:14:09 PM
Apr 6

Not Show Excel Formula Results?


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



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


Monday 4/6/2009 1:40:53 PM
Apr 4

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?


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


Saturday 4/4/2009 11:14:45 AM
Feb 12

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? :)


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


Thursday 2/12/2009 3:18:14 PM
Feb 4

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 )


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

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

Wednesday 2/4/2009 9:05:51 AM
Jan 21

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


This is the lesson for that

Enjoy it

VBAXLMan is here to feed your Excel needs

Wednesday 1/21/2009 6:54:45 PM
Jan 12

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??



Your formula is cut

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


Monday 1/12/2009 12:12:20 AM
Jan 18

How do you conditional format a hyperlink in excel?

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


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

Thursday 1/18/2007 7:59:31 AM
Dec 20

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...


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

Wednesday 12/20/2006 4:36:07 AM
Dec 12

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 ?


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

Tuesday 12/12/2006 11:03:16 PM