Highline Excel Class 02: Excel Formulas & Excel Functions

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.

37 New Custom Functions For Excel. No spyware or malware.

37 New Custom Functions For Excel

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 created a lot of user-defined functions in Excel2000 and I access them
from an .xla file (add-in). I frequently send my workbooks to
colleagues using Excel2000, but I intentionally have not supplied to
them the .xla file. If they do not edit their copy of the workbook,
the user functions display the last-saved results properly. If my
colleagues make edits to cells that affect the user functions, the user
functions fail with #NAME etc. This is good because it prevents my
colleagues from modifying and resaving my workbooks.

However, a colleague now has Excel 2003, and on his machine the user
functions immediately fail (cells display #NAME etc) when the workbook
is opened.

Obviously the problem has something to do with Excel 2003. Any
thoughts how to fix this?


--
goodguy

I would like to be able control the format of a given spreadsheet (primarily hiding/unhiding rows or columns) based on the result of an Excel function located within the same workbook, but not necessarily the same worksheet. The workbook contains numerous Excel "IF" conditional functions, and I would like to be able to control the formatting using another "IF" function if I can. Is there a method to embed Excel Menu commands (like "Format|Row|Hide") in the "value_if_true" and "value_if_false" sections of the "IF" function?

Can you please let me know, how to sort data (in multiple columns across all rows) in excel 2003 using only excel functions (Not using any code or sort option in excel menu).

I am not sure how many rows can be present, as the data will be uploaded to the excel dynamically

Please see the attachment

Can any body help me how to energize a pixel given its X,Y coordinates and colour , in An

Excel Worksheet ,through software.Is it possible in Excel or Throuh interface with VB

Thanking you in Advance

D.S.Rama Rao

Hi,

I was trying to develop the following Excel VBA code. The main aim is for the code to be able to automatically recognize the dynamic inputs tfor the variables and run the Blooomberg BDH function on the variable values. But it seemed that the "" marks seems to be prohibiting the BDH function to take values from the data variables. After running the code, the formula that has been populated in the excel is "=BDH(tickername, pricetype,dtStart,dtEnd)" instead of =BDH formula that accounts for the variable values.

Can any expert enlighten me? thank you a lot!

Sub testBDH()

Dim dtStart As Date, dtEnd As Date
Dim tickername, pricetype As String

'then apply the values you've worked out. e.g.

dtStart = CDate("6/30/2010")
dtEnd = CDate("6/01/2011")
tickername = "ST SP Equity"
pricetype = "PX_LAST"

'then use the variables as follows

ActiveCell.FormulaR1C1 = "=BDH(tickername, pricetype,dtStart,dtEnd)"

End Sub

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.