Limiting formulas based on filtering?
Is there an easy way to build a formula that will only take values into
consideration when the auto format is used. So, if I have a table of data and
a formula based on this table and I filter one of my data columns to some
vause then I would like my formula to automatically update.
Is this possible?
The SUBTOTAL function ignores rows that are hidden by an autofilter (and
optionally, also rows that were hidden manually). It can perform a wide range
of operations, including sums, averages, counts, standard deviations,
products, maximums, minimums, etc. Check E...formula for money additions & subtractions
I am trying to get a formula to calculate payroll deduction for cas
register +/-. When an employee is over by $10 or under by $24, th
difference comes out of thier check.
Example: if somone is -25.00 for the month $1.00 would come out of th
check. If someone is +11.00 then $1.00 would come out of the chec
the +/- for the month is in one cell so i would like the formula to b
able to read that cell and figure out if it is too much or little fo
this is the greatest sight that i have ever found and i hope someon
can help me
Message posted from http...Formula in Marksheet #2
I need help on a 'formula' for my students' marksheet.
For them to pass the whole exam, a student MUST PASS 7 SUBJECTS from 3
The grouping is like these :
A) Must pass ALL 4 subjects in Group A - marks are in cells D7, F7, H7, J7
B) Must pass AT LEAST 2 OUT OF 3 subjects in Group B - marks are in cells
L7, N7, P7
C) Must pass AT LEAST 1 OUT OF 2 subjects in Group C - marks are in cells
Thank you so much
firstname.lastname@example.org...how to make all email address in my address book "safe sender"?
How to make all email address in my address book "safe sender"?
That means I wish all the contact email address in my address book are "safe
sender" and I wish to receive their incoming email without block as spam.
ay <email@example.com> wrote:
> How to make all email address in my address book "safe sender"?
> That means I wish all the contact email address in my address book
> are "safe sender" and I wish to receive their incoming email without
> block as spam.
Version of Outlook? For OL 2003...What is the formula for series: Jan-04, Feb-04, Mar-04, etc.?
Microsoft MVP - Excel
"kitty" <firstname.lastname@example.org> wrote in message
One way of doing it is with the formula below:
If you have a date is cell A1 (01/01/04) and you want cell B1 to sho
you the following month insert in B1 the formula below.
You may have to install the "add in" the ToolPak in order for th
formula to work other wise you will get a #NAME?, error. Hope thi
Fa...Change cell background color based on content that results from li
This could be very simple, but I will lay the groundwork first. I have a
schedule spreadsheet that I import data to from a web based program. There
are existing filters to remove all formatting of the data and remove that
which we do not use. What remains is a non formatted sheet that other
workbooks link to so we can produce daily sheets. Now the data that is
linked on the other sheets may be for example the number 150. It appears
throughout the sheet and I would like to color any cell that contains the
number 150. The problem is I can't search for 150 because it reall...Leave Zero's in cell
when I put three zero's into a cell,.excel automatically changes it back to
one zero. I want to keep the three zero's in the cell. Woiuld appreciate
any advice please
Format the cell as Text (although then the numbers entered are text
rather than genuine numerics) or format the cells to show leading
zeroes (eg a custom format like 000) - note that this is simply for
display purposes as the actual contents of the cell will be 0.
Hope this helps1
On 15 Mar, 10:47, "Ivan B" <nos...@nospam.net> wrote:
> when...making a decision tree using excel!
hello all, i`ve been trying to make a decision tree using excel, the problem
i encounter is that i wish to put information in to b11 which is related to
information in b3, the information in b3 is ether 0 or err and is based on a
"y" or "n" being entered, does anyone know which formula to use which will
print a true of false statement in b11
If you mean print "True" or "False" or your error
So you can use YES, Yes, yes, Y, y and get True
everything else will show False
Or to treat the error in ...time formulas
Can anybody give me an advice
Lets say i put in first column dime of departure and in second one time of
arrival (of course i know have to insert the time ). In third column i get
the time spent somwhere. So far everything is ok. The problem occ urs when i
want the sum of all differences in the third column (rows are months
dates). The autosumm formula wont work and the result is completely wrong.
format the resulting cell with the custome format
> Can anybody give me an advice
> Lets say i put i...How do I stop axis titles from overlapping with negative chart da.
The negative bar charts overlap with the category titles. I've tried offset,
but it won't let me move the text more than '1000' units, and it still
You can find an explanation here.
> The negative bar charts overlap with the category titles. I've tried offset,
> but it won't let me move the text more than '1000' units, and it still
Andy Pope, Microsoft MVP - Excel
Hi Karen -
Double click on the category titles o...Asset with ZERO cost?
is it possible to keep record of asset with zero (acquistion) cost?
these are assets either donated by others or assets where acquistion cost
can not be identified. The assets are 'physically' there and we would like
keep in record.
we tried cost=1 and LTD depn=1 but the will make total assets cost different
from our book.
You should be able to save the asset at zero cost. Can you not?
Frank Hamelly, MCP-GP
NOVA Solutions LLC
sorry, I can add asset at zero cost, but must the Depreciate to Date always
equal Place In Service Date?
&q...Formula for excel example 555 x 15%
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"sari" <email@example.com> wrote in message
...Extra comma and zero-length string
I have a 'simple' concatenation of 3 fields with a comma before the 3rd
field: "FirstName LastName, Academic"
I would like for the finished string to read; "Jane Smith, PhD"
howevever, I've discovered many records have zero-length strings in the
Academic field and are causing the comma to appear when the Academic field is
This is what I have, which works for most, but not all the records that the
Title field appears blank:
Name: [tbl_Investigator]![FirstName] & " " & [tbl_Investigator]![LastName] &am...Pasting Formulas bug?
I seem to be having a strange problem with my Excel (11.2.5) for Mac.
I have pasted formulas for years and never had an issue...until now.
The formulas are simple (addition, subtraction, etc.). I copy the
formula in a cell and paste it to a new one (I even do this with Paste
Special...Formula). What appears in the spreadsheet is the value of
the original cell, not the result of the "relative" formula -- even
though the formula bar displays the correct "relative" formula. I can
only get the correct value in the new cell if I go into the formula bar
and press return at the...Pivot table
Can I develop a formula that I can add to those which you pick from whe
using the wizard ie sum, average, min, max etc
Specifically, I want to add an IF statement to give me a 'flag' i
which to summarize the data with elsewhere. The data behind the pivo
changes (sales data) and I am trying to flag new customers that hav
never worked with us before.....once they have traded with us then the
dissappear as they are now an old customer
Message posted from http://www.ExcelForum.com
no you can't do this
> Can I develop a for...Help Creating A Formula To Copy and Paste Text
Hi everyone. Thanks in advance for any help you may have.
I need to create a formula that searches for a specific word in a
column then cut the word and paste it to another column. Of course
this would be simple if it was the only word in the column, but there
is other text that I would like to remain in the orginal column..
You have a reply at your other thread.
> Hi everyone. Thanks in advance for any help you may have.
> I need to create a formula that searches for a specific word in a
> column then cut the word and paste it to ...How to get Row # in formula?
I have several rows with the same formula but they are seperated, in some
cases, by rows with other information to prevent me from just dragging a
formula down to other rows.
Ex. While in row 13: B13*C13+B13*D13+B13*E13
While in row 20: B20*C20+....
how can I write a formula to populate the "13" or "20" automatically for
whatever row I am in?
You may not be able to drag it, but if you copy row13 formula and go to row
20 it updates does it not?
"DaveR" <DaveR@discussions.microsoft.com> wrote ...query to make a list of products based on delivery history
I have a table which lists all deliveries made of our product. From this
table, I'd like to make a list of all products. My problem is the products
will have many duplicates as they can be ordered multiple times and I just
want a list showing all the individual products that we offer.
Try something like:
SELECT DISTINCT [our product]
FROM [which list all deliveries made];
If you can't figure this out, come back with table and field names.
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Def...Lookup returning incorrect results
Anybody have an idea why I keep getting incorrect lookup results.
In sheet 1, I have a list of "Sales Codes" in col A. In sheet 2, I
have a list of data that with column A for "Sales Codes" and column B
for associated "Sales Reps".
I want to insert a column in sheet 1 that contains lookup formulas to
pull the correct sales rep for each sales code . Both sheets are
sorted in ascending order by Sales code.
My lookup formula in sheet 1, Lookup col B:
The returned results from the above lookup formula are incorrect....HELP!! How to formula the "sheet" link
I have a workbook contains 1+50 worksheet, I want to link each sheet number
to sheet one, besides manually change the sheet # in the formula
=SUM('sheet05'!$H$16:$H$21,'sheet05'!$H$26) ..... up to sheet 50...
Can someone help me to automatic it in anyway?
Thank you very much!!!!
Try a formula like the following:
=SUM(INDIRECT("Sheet"&TEXT(ROW()-4+2,&...Copy a formula formatted as Text In Excel
I have a UDF that returns the formula of a given cell in a text
format. Is there anyway to have excel recognize that the result of
the function is a formula and not text.
Once I click on the cell, hit F2 to edit it and then enter, Excel
realizes it's a formula and then calculates the value,
The formula in the cell is going to be the UDF--not the what formula string
You could add some more steps.
Select the range (if more than one cell)
Convert to values (edit|copy, edit|paste special|values)
what: = (equal sign)
And exce...Currency makes me go Grrrr
How do I change the currency sign? To get UK pounds I changed my computer
settings to UK, as I could not find it under format cells,
currency....surely there must be a better way. I have spreadsheets with $ ,
� and would like to add Euros.
On Thu, 13 Nov 2003 19:02:51 -0000, "Aardvark" <firstname.lastname@example.org> wrote:
>How do I change the currency sign? To get UK pounds I changed my computer
>settings to UK, as I could not find it under format cells,
>currency....surely there must be a better way. I have spreadsheets ...Formula #64
I need to create an excel spreadsheet using a math equation that has one
variable that is the denominator of a fraction. I need to be able to change
the variable every row. Ie - starting variable is 3 and last variable is
101. Any tips would be greatly appreciated!! Thank you
On Sat, 10 Sep 2005 15:31:07 -0700, "V" <V@discussions.microsoft.com> wrote:
>I need to create an excel spreadsheet using a math equation that has one
>variable that is the denominator of a fraction. I need to be able to change
>the variable every row. Ie - starting variable is 3 and ...Replacing Formulas
I am redoing a spreadsheet with several pages. In each page the cells
refer to a sheet that contains a master list of items. THus in the
cells it shows the item but really is a formula for the item to be
retrieved from the master list. I would like to delete the master
list. My question is: How do I get the items on each sheet to not
become blank when I do so? Do I have to retype the items or is there
some way of eliminating the formula and replacing it with the item
I hope I have made myself clear. Thanks for any help.
Bob, select the cells, then Edit > Copy > Edit &...Using formulas for pivot table
I know you have the count field but is there away to input a formula?
For example customer ordered 25 cases and each case weighs 4 lbs and
the end result would need to be total pounds ordered.
If you do the calculation in the pivot table you may not get the result
that you expect. If possible, add a field to the source data, and
calculate the order total there.
Then, add the OrderTotal field to the pivot table's data area.
> I know you have the count field but is there away to input a formula?
> For example customer ordered 25 cases and each case weighs 4 lbs...