|
Sorting by Row Number?
Can you sort a row by row numbers in the formulas of the values of the cells?
For instance, I have a 120x5 table which looks like this:
=K301 =L302 =M303 =N304 =O305
=K301 =L302 =M303 =N305 =O304
=K301 =L302 =M304 =N303 =O305
=K301 =L302 =M304 =N305 =O303
=K301 =L302 =M305 =N303 =O304
....and so forth
I'd like to sort the rows in my ta...
|
1 |
30.09.2006 03:36:01 |
|
|
Can columns be hidden accross multiple sheets in an Excel workboo
Is it possible to group and hide colums in an Excel workbook across multiple
sheets at once without having to group and ungroup colums for each page? I
have a workbook with thirty sheets that I use to keep track of 25 peoples
production during a month. If a person leaves, I would like to be able to
hide that column once and have that column hidden on the other pages. I am
using Excel ...
|
12 |
30.09.2006 06:38:45 |
|
|
Search / Mid function
Hi, I want to return the words/ string of words after the last divider "/".
e.g. in Column A, there are
Society/Education/University/USA
Society/Education/University/USA/University of Chicago
Family/babies
Leisure/Outdoor/camping
I want to return:
USA
University of Chicago
babies
camping
in Column B.
The number of "/" is variable in each row.
I only know how to return the valu...
|
5 |
30.09.2006 06:33:00 |
|
|
Display Month - 1
I have a cell that I would like to display the three letter month minus a
month based on the current day
So today is 09/29/2006, I would like the cell to contain "Aug"
Is this possible.
...
|
11 |
30.09.2006 04:18:55 |
|
|
Freqencies over Time, in Columns -- Easy Question
I created a histogram using the FREQUENCY function for a dataset I'm working
with. As I was thinking things over, I'd like to see how the frequencies
"built up" over the period of time that the data was collected.
The catch is that I want my bins to go horizontal and the freqs to be able
to be pulled down vertically, like so:
10 20 30 40 50 60 <--Bin...
|
1 |
30.09.2006 03:27:01 |
|
|
count text in column a if column b >= 25
I'm trying to count the # of clients who have a system status of out of
business in column A that have less than 25 employees. Column B has the # of
employee.
Col A Col B
Out Bus 15
Sold 32
Out Bus 57
IH 8
Out Bus 22
I have tried variations of sumproduct, countif, but nothing I'm doing is
working. I'm su...
|
2 |
30.09.2006 02:40:23 |
|
|
How do I calculation vacation accrual?
I am attempting to calculate vacation accrual so that I can enter into
QuickBooks.
Vacation accrues as follows:
1) employee gets 1 week (40 hrs) after 6 month employment
2) employee receives 1 week vacation for every 6 months worked or portion
thereof
3) maximum vacation an employee can accrue is 4 weeks (160 hrs)
4) employee is paid bi-weekly (26 pay periods/2080 hrs annually)
I am ...
|
2 |
30.09.2006 02:06:02 |
|
|
VLOOKUP MULTIPALS
Here is my formula:
=VLOOKUP(A5,INDEX('RUNNING DATA wkly'!3:3,1,MATCH($G$1,'RUNNING DATA
wkly'!1:1,0)):INDEX('RUNNING DATA wkly'!6000:6000,1,MATCH($G$1,'RUNNING DATA
wkly'!1:1,0)+2),3,0)
and I need to (SUM) up my (VLOOKUP) results I am only picking up 1item out
of multiple items on a list.
...
|
5 |
30.09.2006 00:34:01 |
|
|
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw
I have a match formula that shows correct data from a second exel file but
when I close the second excel file the file with this formula replaces the
correct data to show #value. How can I chg the formula to not require the
second file to be one inorder to have the correct data?
OFFSET('U:\Department\General Accounting\Budget\2007\Data Files\[2007
Depreciation.xls]Software Amort'!$B$...
|
3 |
30.09.2006 00:26:09 |
|
|
SUMPRODUCT question
I have the following equation:
=IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data
Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19)))
It works if the last range doesn't have any N/A values in it. What do I
need to change to get it to ignore the N/A values?
Thanks
...
|
8 |
29.09.2006 21:44:19 |
|
|
More than 16 nested IFs!!!!
Hi all,
I used Chip Pearsons method of overcoming 7 nested IF statements using named
ranges and this worked (http://www.cpearson.com/excel/nested.htm) but now I
have some additions to my formula which makes the total number of IFs 16 -
how do I get over more than 7 IFs in each of my seperate named ranges???
Here is my whole formula!!! It calculates the lowest margin for a range of
se...
|
3 |
29.09.2006 21:44:01 |
|
|
Adding VLOOKUP
Greetings,
I have the following formula, but it only returns one value although the
search criteria appears more than once in the array:
IF(ISNA(VLOOKUP(A14,'21'!$B$8:$H$53,7,FALSE))=TRUE,0,VLOOKUP(A14,'21'!$B$8:$H$53,7,FALSE))
Currently, this formula looks up a name in a different worksheet within a
workbook, in this case the worksheet tab labeled "21". I want it to add the
dollar ...
|
5 |
29.09.2006 21:17:29 |
|
|
hyperlink
How can I create a hyperlink in a cell in excell to a page in my outlook
contacts?...
|
1 |
29.09.2006 21:04:02 |
|
|
lost the ability to bracket negative numbers
I have recently gotten a new computer. I am running windows 2000. My
harddrive was "mirriored" from old computer also running windows 2000.
Now in excel I cannot format negative number with brackets.
Also not available under custom.
I have uninstalled and reinstalled. I am using Office 97 with an upgrade to
Office 2000 standard.
If I buy Office 2003 (not an upgrade version) will I get...
|
4 |
29.09.2006 20:55:05 |
|
|
Exporting data (Data Table) to Excel
Hi,
I develop a windows application in C# and have a form with Data Table (Data
Grid). I need to export it to Excel to specific path (selected by the user in
SaveFileDialog). Can anybody tell me how I do it?
Code example will be more than welcomed.
Thank you....
|
4 |
29.09.2006 20:52:19 |
|
|
Pulling multiple values from a list based on a wildcard search value?
I need some help w/ some excel coding. After reading the article "How
to look up a value in a list and return multiple corresponding values"
by Ashish Mathur
(http://office.microsoft.com/en-gb/assistance/HA012260381033.aspx) I
would like to use this code but modify it so my "search" value can
include wildcards...
So in reference to the example at the Microsoft website (above),
instead of...
|
5 |
29.09.2006 20:46:40 |
|
|
Shortened formula
Hi,
I have a need for a formula whereby the follow criteria applies: if D6 = 0,
return 0, if D6 is between .01 and .49 = .49, if D6 is between .50 and 1.24
= .99, if D6 is between 1.25 and 2.24 = 1.99 and so on. Is there a shorter
formula that someone else could understand easier than this one which works
but seems long winded?
=IF(D6=0,0,IF(D6<0.5,0.49,IF(D6<1,0.99,IF(D6-IN...
|
2 |
29.09.2006 20:03:01 |
|
|
need formula for % of times text appears in row.
Hi. I need help with with calculating the percentage of times
a specific text appears within a row.
The formula needs to be relative, so I can copy and paste it
into successive rows.
e.g. Based on the text in A2:D2, what are the percentages
for “x,†“y,†“z†in that range?
____________________________________
__ |_A_|_B_|_C_|_D_| _E_ |__F_ |_ G_ |__
1 | | ...
|
3 |
29.09.2006 19:47:02 |
|
|
=ROUNDUP(INT(0.xxx))
This function should always return 0, correct?
Ex.: =ROUNDUP(INT(0.9999)) is really =ROUNDUP(0)
A colleague claims this is a bug in Excel; I claim this is correct.
Dave
--
Brevity is the soul of wit....
|
8 |
29.09.2006 19:28:57 |
|
|
table lookup
Can someone help me in creating a function to look up the following data:
Table:
Year 1 2 3 4 5 6
2,002 -137.15 -232.68 420.57 529.72 862.24 -103.23
2,003 985.12 985.05 999.49 982.1 1195.98 1005.19
2,004 274.52 547.62 468.69 442.84 337.74 561.39
2,005 474.75 502.9 516.04 445.04 103.68 870.25
2,006 574.68 506.82 506.59 502.93 57.6 213.25
I am trying to look up the min value per ...
|
4 |
29.09.2006 19:17:02 |
|
|
Formula
I am trying to input a formula that when I subtract the contents of one cell
from another if the sum is negative, I want a zero to show in the cell. If
the sum of the cell is a positive number then I want the sum to show in the
cell.
This is what I have tried =IF(G4-H4<=0,0). The only problem with it is
every time the sum of the cell comes up positive it gives me a false
statemen...
|
4 |
29.09.2006 19:08:02 |
|
|
Lookup Closest Value
Is there a way that I can lookup the closest value of a given number in a
table of data using Index/Match?
I am looking for a formula that will provide the same functionality as
setting the VLOOKUP Range value to true but allows me to have more flexibilty
as to the data I can work with. Any help with this would be greatly
appreciated.
Chad...
|
4 |
29.09.2006 18:58:41 |
|
|
Help with complex average/if statement
My worksheet has a tab for each quarter, with data on each tab as in
the example below:
REGION SCORE
Asia 3.4
Asia
Asia 4.0
EMEA
EMEA 2.7
NA 3.9
NA
etc.
In other words, there are multiple regions, and each row may or may not
have an associated score.
On each tab, I've successfully averaged the scores by region u...
|
2 |
29.09.2006 18:33:35 |
|
|
Want average sales numbers for last 12 months.
Am I allowed more than one question? :)
Maybe someone here knows.
Looking for 3 items from a spreadsheet.
In Column A: Have dates in no particular order for shipments spanning 5
years.
in column B, have shipment dollars for the various dates
A. Looking for the average of shipment dollars for the previous 12
months (looking back from todays date).
If it finds 16 events ocur...
|
3 |
29.09.2006 18:16:52 |
|
|
Counting rows based on multiple columns
Hi everyone. I have two columns of various information and I want to count
the number of rows that have both "accept" in column 1 and "february" in
column 2. I want to avoid having to create another column of data. Thanks
for any help....
|
2 |
29.09.2006 18:06:31 |