|
Nested IF functions beyond eight items?
I am trying to write a formula using the IF funciton. The formula itself is
fine. My problem is, though, I need more than just seven conditions. I
actually need 12
The formula currently reads like this:
=IF(A12<=0,0, IF(A12<=5, 2.5, IF(A12<=10,5, IF(A12<=15, 7.5, IF(A12<=20,10,
IF(A12<=25, 12.5, IF(A12<=30, 15, IF(A12<=35, 17.5))))))))
As can be seen, this...
|
4 |
25.09.2006 17:58:07 |
|
|
Can I protect a document and still open / close groups
I have a document that contains rows in a group. When I have my sheet
protected, I am unable to open the group if it is minimized and unable to
close the group if it is maximized. Is it possible to open and minimize a
grouping when a sheet is protected....
|
2 |
25.09.2006 17:47:42 |
|
|
Need ability to get cell address of max value in range
When looking at a range of cells, I need both the contents of the maximum
value in the range and the actual cell address of that cell. For example, I
have a series of readings from A1 to Y31 to represent every hour and day of a
month. If the highest reading is in the 11th hour of the 15th day (obtained
by the function MAX(A1:Z31) then i need to be able to get the actual address
of K15 ...
|
12 |
25.09.2006 17:43:01 |
|
|
Helpful New Functions: ALL and ANY
I'd really like Excel to have an All(range, criteria) function, as used in a
number of programming languages. For instance:
A B C D
1 y y y n
2
3
=ALL(A1:C1, "y") --> TRUE
=ALL(A1:D1, "y") --> FALSE
In a similar vein, the ANY function would be quite handy:
=ANY(A1:C1, "y") --> TRUE
=ANY(A1:C1, "n") --> TRUE
=ANY(A1:C1, "o") ...
|
7 |
25.09.2006 17:38:36 |
|
|
Naming Cell on differnt worksheet
I make a DARTS scoring workbook with 26 Teams, each Teams as is own sheet,
So every week i enter the scores on each sheet, then using a namecell, i
copy to the master sheet....
What i need to do, since i have many teams and many week to enter.
On each sheet, i need to enter and name 26 cell diiferently.
EX: A1=T1WK1 - A2=T1WK2 AND SO ON!
As you see the naming is almost the same but...
|
1 |
25.09.2006 16:52:02 |
|
|
display the cell value in the next 33rd cell
I need some help with writing a 'search & display' formula:
Scenario:
cell A9 has a value of $4600
-> set cell T2 to '=A9'
Question: what formula do I put in T3 if I want it to return the value in
cell A42 (basically, every 33rd cell from a range of cells). This is for a
monthly report where the starting cell is not always 'A9', and the locations
of the 'next 33rd cell' varies fr...
|
5 |
25.09.2006 16:23:01 |
|
|
Adapting MAX function
I have created a forumla in excel which selects the the highest value in a
range based on certain criteria contained in a neighbouring cell, it is shown
below:-
=MAX(IF(YOT!$B$3:$B$150="England",(YOT!C$3:C$149)))
Can this formula be adapted so it does not pick the highest value, but would
pick up the row heading of the row with the highest value, in this example it
would be the value...
|
5 |
25.09.2006 15:53:02 |
|
|
Lookup a row and column and return value
Hi all,
I have a spreadsheet where I have a list of part numbers (in rows) and then
a series of percentages across the top in columns e.g.
Parts 10% 20%
123 52.50 33.50
178 33.37 23.44
I then have another spreadsheet that performs various VLookup's on the part
number and returns key data. However this spreadsheet allows me to
determine the percentage e.g. 20%. What I need...
|
3 |
25.09.2006 15:38:45 |
|
|
How do I use more than one value in Conditional Formatting?
I have more than 3 conditions that I would like to apply, is it possible to
use = with OR in conditional formatting?...
|
5 |
25.09.2006 15:36:02 |
|
|
Need big help with look up....is this possible
Hi,
I'm building a sheet that pulls data from another worksheet based on a
customer number, so when I enter a customer number into cell it will pull
specific information. The part I'm stuck on is this....There are varying
numbers of rows below each customers' data depending on the number of years
they have been a customer....one row per year, starting with the current
year. I want t...
|
6 |
25.09.2006 15:31:24 |
|
|
skip errors in an average
I'm trying to get an average of a series of averages. Some of the cells in
the series have a 0/div error, which is ok for the series. I can't get the
average of the averages formula to ignore the error cells. Here's what I'm
trying:
=AVERAGEif(M3:M58,"<>0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie...
|
9 |
25.09.2006 15:05:02 |
|
|
Conditional Formatting
I have a sheet that has conditional formatting that anything after a todays
date is red. What I really need is for that date that it looks at to never
change.
For Example:
If today I run it and it uses 9-25-06 and then tomorrow I run it again the
date is then 9-26-06 that it uses. How can I use a set date?...
|
2 |
25.09.2006 15:01:02 |
|
|
tracer arrows not visable
The tracer arrows are not visable when clicking on the dependant or precedant
button. I have already enabled the tools-options-view-objects - 'show all'
button. ...
|
1 |
25.09.2006 14:59:03 |
|
|
Countif & Sumif with Multiple criteria
1. Countif with multiple criteria. I have tried sumproduct as follows...
=SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
but this gives me a result as #NUM!
The cell is set to general.
What I am counting is in column A tell me how many cells have "b" and how
many cells also have "true" in G column. I only want to count the cells that
have both.
2. Sumif with multiple criteria. ...
|
7 |
25.09.2006 14:36:37 |
|
|
How do I repair broken links that worked with Excel 2000?
I have 3 excel documents linked between each other. They worked perfectly
fine with Excel 2000, but I need to update to 2003 and these links appear
broken. I try to update with F9 with no good results, and replacing '='
instead of '=' to renew calculations, starts to do it ok, but the document
is large and I get an "Out of resources. Close some apps" message.
Can someone help me with t...
|
3 |
25.09.2006 13:53:02 |
|
|
formulas within a work book
I want to know how to type a number from 1-27 in a cell and have it
automatically put in numbers in that cell and one other cell off of another
sheet in the workbook.
Example: If I type in 1 in D3, it would automatically enter 4-3 1/2" in D3
and 4-400KCMIL in E3. If I type in 5, it would automtacially enter 2 1/2" in
D3 and 6-250 KCMIL in E3.
THANKS!!! ...
|
2 |
25.09.2006 13:48:51 |
|
|
Sum column based on value in each row, if two cells equal, or if date is within time period
I need help - I actually am trying to do two separate functions, but I
think the formula should be similar for both.
#1: I am trying to sum a column if two cells in each row are equal.
For example:
Date N. Hours D. Hours Total
Row1 1/1/2006 3 1 4
Row2 4/4/2006 2 ...
|
9 |
25.09.2006 13:40:53 |
|
|
SUMPRODUCT WITH A VARIABLE CRITERIA?
HI, I WOULD DO SOMETHING LIKE THIS:
=SUMPRODUCT(--(A2:A500="x"),--(B2:B500=D1),C2:C500)
WHERE (CELL)D1 IS VARIABLE THAT I INTRODUCE
THANKS IN ADVANCE...
|
3 |
25.09.2006 13:29:34 |
|
|
Tracking Date/Actual Working Time Elapsed
i have obtained a solution from this Discussion Board which more or less does
what i require.
I have two dates the start date and time (A1) and the end date and time (B1)
Our standard working day is 9:00 to 17:00 with lunch from 12:30 to 13:30
monday to friday.
I need to find the time spent working on a particular project in the format
[h]:mm between the values set out in A1 and B1
...
|
2 |
25.09.2006 13:14:02 |
|
|
Insert function
Hi
I am running Office Professional 2003 - 11.8033 sp2.
When I try and link a cell from one sheet to another the data copied does
not reflect the original cell and random data is copied. I have tried this
on a new document and have the same problem.
I firstly repaired then reinstalled office and this has not worked.
I have also tested the same document on a colleagues machine and c...
|
1 |
25.09.2006 12:31:52 |
|
|
add time
I want to add a column of times in a time sheet. I can get the hours worked
but can't seem to get the column to add totalhours...
|
7 |
25.09.2006 12:30:01 |
|
|
2006 West Coast Excel / Access User Conference
2006 West Coast Excel / Access User Conference - October 25-27, 2006
http://www.exceluserconference.com/WCEUC.html#Tentative_Schedule
Mike Alexander
Alison Balter
Dick Kusleika
Simon Murphy
Jon Peltier
Bob Umlas
The regular registration period is coming to a close on September 30th. Be
sure to register this week if you would like to get in before the late
registration period begin...
|
1 |
25.09.2006 12:09:00 |
|
|
Calculate working hours from a start and finish time over several
I am trying to calculate the working time in the format [hh]:mm for assigned
tasks so that i will be able to log the rate at which different tasks can be
completed and help schedlue for the future.
I was planning to use a macro to copy and paste the value from a cell
containing the value =NOW() to create the start and end time. which is easy
enough. my problem lies with getting the num...
|
2 |
25.09.2006 11:46:04 |
|
|
text and formula within the same cell
Hi. I want to include text and formula within the same cell in Excel.
Does anyone know how I can do it?
I am trying to acheive (A4*B7)membership+(C3*D10)journals so it
appears as a number (say 100) but anyone looking at the formula can see
what it relates to.
Can anyone help please?
Thanks
...
|
8 |
25.09.2006 10:03:45 |
|
|
SUMIF across multiple worksheets
I am collating a summary stock movement which using the sumif function i.e
SUMIF Sheet1!b$1:b$500,a5,a$1:a$500)
Is there a way to shorten the formula as I have presently 40 sheets for one
week which is causing me problems space wise (and hurts the eyes when looking
at the formula) as the a5 is a rolling cell and there are 945 varying cells
in the summary worsheet....
|
8 |
25.09.2006 09:48:01 |