Adding if to Average If Array function
I recently had help understanding the following formula
Thanks to Luke M, Pete UK and David Biddulph who provided the explanation.
I've now been asked to add to this formula so that if either Q3 or Q4 is
greater than 4 then the average cannot be lower than 3.
Q1:Q35 is the named range score.
This is really complicated because I still need to have an average and I'm
thinking that the If part is non array but the average part still needs to be
Can you mix and match ...Searching for Image and adding path
I am working on a database that has several records. Each record has a
different image associated with it (or will).
I will not be the only person using this database, so I want to make it very
simple to add images to records. I know that saving the image in the
database isn't the best idea for Access 2003, so instead I want to have the
path stored (which will make mail merging work too in Word).
This is what I want:
In the form, on the tab where the image information is, I want 3 things...
- The image itself displayed, once the user adds it
- The PATH of the image in a text b...Averaging Selected Records
I have a continuous form with lots of data on it.
In the form footer I would like to average certain fields by checking a
checkbox next to the fields (in the detail section) I would like included in
I am having a brain fart on this and just not getting it to average the
Can anyone point me in the right direction?
"Lythandra" <Lythandra@discussions.microsoft.com> wrote in message
> I have a continuous form with lots of data on it.
> In the form ...I have added FrontPage 2003 and now I can't update my Pub2003 site
Ever since I installed FP 2003, I have not been able to update my Publisher site.
Does FP cause a known conflict?
Dedicated Virtual Assistant at http://www.world-wide-virtual.us
Hi Damzish (email@example.com),
in the newsgroups
|| Ever since I installed FP 2003, I have not been able to update my
|| Publisher site.
|| Does FP cause a known conflict?
There are no known issues. What appears to be the problem? What is
happening? Are you getting any error messages?
Microsoft Publisher MVP
~pay it forward~
T...I am looking for a function for linear interpolation
I am looking for a function for linear interpolation
Look in HELP for the LINEST() function
Microsoft MVP - Excel
"azad" <firstname.lastname@example.org> wrote in message
>I am looking for a function for linear interpolation
...Can I use the NOW() function in a formula?
I have a cell B1 that has NOW() time and another cell B2 that has NOW() date.
I am trying to have data from another cell F1 brought in to the destination
when a given date and time occur.
This formulas does not work.
The Clock is continuously active / always changing.
Any suggestions would be appreciated. Thanks.
The NOW() function returns both a date and a time. Even if you format the
cell to display only date/time, all the data is still retained. NOte that if
you did want just the d...Array formula
This formula works fine, up until the separated part, then it just does
nothing(I put the spaces in just to clarify the problem area for this
discussion). No matter what order put the arguments in, it only works up
to the same point. Is this just a case of to much info? How do I extend
this formula (I need it to be even longer the above example).
Try replacing SUM by SUMPRODUCT and do a normal completion with just ENTER
since it is not an array formula
"atom" <email@example.com...Sum function
Operating System: Mac OS X 10.5 (Leopard)
I am trying to put together a spreadsheet for a business plan and am having problems with the autsum and autofill functions. Firstly, even though I do a simple autosum, if I change a value in a cell, the sum does not change. Secondly, if I try to autofill across columns, the values of those columns simply mirror the value of the first. In other words it copies it. I am at a loss how to get round it as I have been working w Excel for years and have never had this problem. I have experimented with a new worksheet with ...Need help with correct function or input for this problem/scenario
I have a situation where I am creating a borrowing base for a company. (I am
going to shorten numbers to make it easier.)
There is a $300 loan.
Type A loan can borrow up to $300.
Type B loan can borrow up to $150.
Type C loan can borrow up to $50.
I need a formula to input in excel that will give me a borrowing base for
type B and C loans. For example, to borrow in type B loans, I have to check
1-what's outstanding on the entire loan.
2-what's outstanding on type B loans.
3-Take those two numbers and figure out what can be advanced under the
answer to those questions. (I.E. $2...sum function usin an IF statement
Bit short on detail, but maybe
as an array formula, committed with Ctrl-Shift-Enter
again an array formula, where rng1 and rng2 are the same size.
(remove nothere from the email address if mailing direct)
"jimk" <firstname.lastname@example.org> wrote in message
...Using Filter string with IF function
Is there a way to use a filter string with the IF function?
I have a spreadsheet that looks like this:
Invoice No. Invoice Date Invoice Currency Invoice Value
1 dd/mm/yyyy USD 1234.56
65 dd/mm/yyyy USD 2345.67
74 dd/mm/yyyy EUR 3456.78
88 dd/mm/yyyy USD 4567.89
when I filter on the currency: is there a function such as
Assuming row 1 are t...adding numbers in an excell chart automatically
How to create an ongoing addition of numbers in an excell chart. Or sum of
Numbers I should Say. I need the info for work. I think there is an easier
way than how I am doing it.
Worksheets are the things to use for adding numbers. That's not what a
chart is designed to do.
"sanicay" <email@example.com> wrote in message
> How to create an ongoing addition of numbers in an excell chart. Or sum of
> Numbers I should Say. I need the info for work. I think there is an
>...Using client PC to find the functnl level of my log-in AD domain
Using Windows XP service pack3 to login to AD domain.
Running the GPresult from the command prompt returns the following:
RSOP results for TMS\TABATAJ on PD9403355 : Logging Mode
OS Type: Microsoft Windows XP Professional
OS Configuration: Member Workstation
OS Version: 5.1.2600
Domain Name: ABC
Domain Type: Windows 2000
Site Name: DATA-HUB-SITE
Local Profile: C:\Documents and Settings\tabataj
Connected over a slow link?: No
Can I conclude that the Functional level ...Another Issue With A Function
Ron Rothstein suggested and I used this function in my Excel 2003
Public Function QBRushYds(W, Q)
QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10
In Worksheet Totals Cell B56 thru B59 I have the following four
In Worksheet Week1 Cells C3 thru C6 I have the following values:
The function correctly calculates the values:
If I change any of the values in C3 th...And function into If function
I´m trying to get this function to work. But I´m getting an error message.
=If(and(100/I6)*I7>101); I7<I6; 1; 0)
What the function is supposed to do is to detect peak values in chart table
that are measure errors from a measuring intrument and count every peak as 1.
This is how the function is ment to operate:
If the percent of the difference of the rise from the former cell to the
next cell in the sequence of data is larger than 101% AND the value of this
cell is smaller than the former cell then true=1 False=0
Im using a norwegian version of excel and I´m no...add individual data points to a bar chart of averages
I have groups of data points in categories. I can easily calculate the
average in each category and show these as a bar chart, but is it possible to
show the individual data pioints in each category as well?
How are you plotting the data points - as columns or line? and also could
you show us dummy data layed out so we can understand what you data looks
like. If you plot a clustered column chart you could show the average for
each cluster as a line within the cluster or as a single point.
> I have groups of dat...Sum Function (was summing 13 cells to the left)
Trying to compose a formula that will only sum 13 cells to the left (13 months of data). When columns are added, I still only want to sum thirteens months of data. Can this be done without constanty changing my formulas? Make sense
see my reply to your old post
Neil S. wrote:
> Trying to compose a formula that will only sum 13 cells to the left
> (13 months of data). When columns are added, I still only want to
> sum thirteens months of data. Can this be done without constanty
> changing my formulas? Make sense? ...adding rows #2
How do I add rows without changing cell location in the formula.
The data for the formula is always located in cell B4 and if I
add a row on top of B4, I want the formula to remain with B4.
Right now, if I add a row, the cell in the formula goes down
Hope for help
If I understand, you're adding a row higher than B4. This shifts B4 down,
but you want the "new" B4 to be the data for the formula. If this is the
case you could try this:
That is, as long as you don't add a new top row.
I am currently creating a page that has a column of calculations in it.
The calculations are amounts of Future options. at the bottom of the
column I calculate an average 6 month total by simply suming 6 months
into the future. The question I have is how do I automate the Total sum
number at the bottom, because every time I update my worksheet I have to
redo all my calculations because the previous months value is now 0
because there is no future value anymore, so I have to rename my range
and do my calculation over again. I originally thought of the Hlookup
function but I can't get it to ...Rank Function? #2
Why would my RANK function be returning #DIV/0! ????
Didn't know this was possible. Anybody run into this
> Didn't know this was possible
It isn't - so Excel is saying that you are trying to do the impossible
Your code will probably need to check for and exclude zero values
Message posted from http://www.ExcelForum.com
...identical smtp address - more than one user object
I have just come back form a client site, inherited, where we were
trying to work out why certain email addresses were not working as expected.
Basically they had a user object in AD - firstname.lastname@example.org - with the
primary smtp set as email@example.com with no delivery options set.
However when I examined another user object in the same active directory
I found the identical smtp address listed in their email address list.
How is this possible?
I was under the impression that you would not be allowed to create the
smtp address as it was already in existence.
SBS 2003 BTW.
...Error msg. "Failed to grant access to regenerated view" when adding field to schema using deployment mgr.
I'm running into a error simply creating a new field in the schema for
The error tells me to go to the event viewer, which in turn tells me the
Failed to grant access to the regenerated view Lead after inserting new
Is the system trying to add the field to a view in addition to the table?
How can I proceed with this?
Any help would be appreciated.
Trying to work out the best way to get an average answer based on a
set of answers
The answer will be 1 - 10 across a number of questions
B C D E F
Answer Q1 Q2 Q3 Q4
1 1 1 3 3
2 1 0 1 2
3 0 1 3 3
4 1 0 1 1
5 1 1 6 7
6 2 2 5 2
7 2 3 3 4
8 2 1 7 6
9 1 2 1 3
10 2 2 8 7
Total 13 13 38 38
Average ? ? ? ?
What is the best way to get the average response to each question.
i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure
this is right....Average days to pay #3
I just need a help from you all. Can any one of you please provide me the
formula for caluclating average days to pay?
And is debit memo's are considered for it? Please clarify.
Thanks in advance.
Please find below the information on how the Average days to pay is
When and how is the Average Days to Pay calculated in the Customer Credit
Summary window (Cards|Sales|Summary|Credit Summary)?
The Average Days to Pay field is updated when a debit document is fully
applied. The average is recalculated as the document i...Basic IF function help needed
This is a multi-part message in MIME format.
I'm using Excel 2002 for the first time and trying to place a function =
into a cell. I 'm using IF(N44=3DL, M44-L44, L44-M44) .
Which is supposed to mean If the letter L is in cell N44, then subtract =
number in L44 from number in M44. Otherwise subtract M44 from L44.
If have 2 main questions...First, how to get the result of the function =
to show. Right now I see the function typed out in...