37 New Custom Functions For Excel. No spyware or malware.
37 New Custom Functions For Excel
I have a table that was created in Word 2000 that has been copied into Excel
2000. There is a column (A) that contains a list of countries and I would
like to count the number of times each country appears in column A. In Column
B I typed =countif(a1:a20,"Japan") and the value is returned.
The problem is that some cells in column A contain more than one country,
for example Japan, Italy. If I use 'countif' it returns the value as zero. Is
there another formula I can use?
The desired results is to determine how many times the country is repeated
and then to generate a chart based on the results. This is all text and other
than the 'tally' there are no numbers involved.
Any help would be appreciated.
Occasionally I type in a simple function or formula such as =AA7 and Excel
treats it like a literal instead of a function. If I open another
spreadsheet and type in the same formula it correctly gives me the value of
cell AA7.
Any clues.
HI,
is it possible to export all Functions/Formulas/Validation list formulas etc. for each cell for an Excel Workbook? Would be useful as a backup and/or reference for everyhing a given Cell in the Worksheet is holding (in case of multiple things).
Joe
I am creating a spreadsheet and want it to work so that I put monday's date
in, and the rest of the week's dates populate automatically. I have done this
before, so I know how to do it - but I changed the function (by mistake) and
can't remember which function I need to input this formula...
I have tried different functions, but the formula appears in the cell,
instead of the date (e.g. I enter 11/04/2005 in A1, then put formula in A2
[=A1+1] - but this formula is in the cell, not "12/04/2005", like I
wanted....)
Any help appriciated.
Thank you,
Rob,
Teesside,
UK
I am not a very experienced user of Excel and I know some of these problems may be trivial to a lot of others on this forum - but I appreciate your time in reviewing and helping!!
First Problem:
I need a little help with the same employee time sheet that I am working on in Excel 2007. I have a dropdown box in Cell E1 that lets the employee select their name. Example, if the choices in the drop down a Smith, John Jones, Paul Nelson, Bob and Hall, Bill. So the names are Last Name, First Name. Once the employee chooses their name, I would like Cell G1 to display "Hello (E1). Welcome to AA Company." (where E1 is the employee's name but First Name Last Name - so it is reverse order of the choices in the dropdown). If E1 is left blank, I would like Cell G1 to display "Hello. Welcome to AA Company."
Second Problem:
How can I get rid of #VALUE! in a cell without messing up the formula of that cell or the cell it corresponds to??
Please help.
Column A lists all of our Group Distribution Lists in alph order and Column B lists the names of the users that belong to that group DL.
So if there are 3 people in a group DL, each person is listed in Column B as a separate line item which then also displays that Group DL on 3 separate lines.
Column D is a list of user names in alpha order appearing only once per line.
Columns E - HM Lists the Group DL's.
Columns E - HM are where I need the returned value of "x" entered in the corresponding cells when the lookup returns a value.
I.E. - So if Group: z-it-AAD-ChangeControl is in column A and Jim Dawson is in Column B, I want an "x" entered into the cell where z-it-AAD-ChangeControl and Jim Dawson intersect somewhere in columns E - HM.
I have attached a zip file w/ the excel file in it, please help me if you can.
I have been struggling with my excel. Can someone tell me how and what i can do find the fees earned monthly(13/3 weeks) for each child? The family's income and number of children affect the fees each child's fee. (Assumption: All family's children are 2-5 years old with 2 parents)
*Maximum charge for each child is $300 and the minimum is $40 a month.
EXCEL FILE...CLICK HERE!
Thanks!!
We have an excellent in house developed add-in(xlam) that automate many processes.
Unfortunately we have some excel templates with the same UDF functions locally, i.e
VB:
Public Function rng(ByVal dx As Double, ByVal dy As Double) As Double
rng = Sqr(dx ^ 2 + dy ^ 2)
End Function
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
The above function exists in both add-in and local workbook.
In Excel 2003 this was not a problem.
But in Excel 2007 the formula is referenced to the add-in (xlam) and not the local workbook VBA i.e
VB:
= 'C:\Documents and Settings\xx\Application Data\Microsoft\AddIns\isXtools.xlam'!rng(.....
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
.Is there a method for setting the priority of which the formula is referenced?
I have tried disabling the add-in in the workbook_open() procedure and enable on the workbook_Beforeclose() without luck.
The AVERAGEIFS function returns the average (arithmetic mean) of all cells that meet multiple criteria.
The SUMIFS function adds the cells in a range that meet multiple criteria.
The COUNTIFS function counts the number of cells within a range that meet multiple criteria.
The AVERAGEIFS function in cell A8 returns the result of 12.5
1.First Argument: The Averaging range, 12.5 is an average of 10+15 appearing in that range.
2.Second & Third Argument boxes: Criteria range 1 + Criteria1, 10, 15 & 20 meeting the criterion ">2" in List range.
3.Fourth & Five Argument boxes: Criteria range 2 + Criteria2, 10 & 15 meeting the criteria ">5" in Number range and ">2" in List range.
The SUMIFS function in cell A9 returns the result of 25
1.First Argument: The Sum range, 25 is the sum of 10+15 appearing in that range.
2.Second & Third Argument boxes: Criteria range 1 + Criteria1, 10, 15 & 20 meeting the criterion ">2" in List range.
3.Fourth & Five Argument boxes: Criteria range 2 + Criteria2, 10 & 15 meeting the criteria ">5" in Number range and ">2" in List range.
The COUNTIFS function in cell A10 returns the result of 2
First & Second Argument boxes:
Criteria range1 + Criteria1, 2 is the number of cells meeting two criteria in List range.
Third & Fourth Argument boxes:
Criteria range 2 + Criteria2, 10 and 15 meeting the criterion ">5" in Number range.
