Zeroing out data but leaving formulas.
I have a spreadsheet that I calculates monthly data and puts it into a year
end management spreadsheet. How do I clear the cells of data (zero out) but
leave the formulas intact so that I can use the spreadsheet again in the
You should be able to just delete the static data (the data that the formulas
are using to make calculations). To toggle the formula view (see the
formulas) use Ctrl+~
> I have a spreadsheet that I calculates monthly data and puts it into a year
> end management spreadsheet. How do I clear the cells of data (zero...Excel QUOTIENT ; That's the matter with the formula ......
1. Enter the following formula into a worksheet cell :-
2. = QUOTIENT (36,16) + RQP
3. Where RQP (=26) is a Named Formula (well defined).
4. The given formula appears to calculate well until the partial
evaluation in the Formula Bar gives #NAME? (that's apparently an error
5. That means that when the function QUOTIENT is applied in a larger
formula, it would stall thereof.
6. There are inevitably dithering moments at the expense of
7. Please share your experience. Regards.
What's the definition of RQP?
When I put 26 in a cell and name that cell RQP....Pie Charts #10
I need a pie chart to show quarterly data for 5 different departments.
Each quarter will display in it's own pie chart. Each of the 4
quarterly pie charts will be sized proportionately to the year end
total. A fifth pie chart will display the TTL data for each
department. Is this at all possible? Is there a better way to show
Qtr 1 Qtr 2 Qtr 3 Qtr 4 TTL
Dept 1 10 30 20 15 75
Dept 2 15 25 25 20 85
Dept 3 20 20 15 30 85
Dept 4 25 15 8 30 78
Dept 5 30 10 19 35 94
100 100 87 130 417
Thank you in advance!
On Aug 6, 3:57=A0pm, valerie <vgar...@carletonls.com> wrote:
&...all grids to display the total number of records returned by the q
Currently only first (up to) 100 records are displayed with an indication
that query results contain more pages, the only way to see how many records
are in the list is to click next page button multiple times (if the total
number of records is not too high).
The customer is asking to have the bottom statusbar that shows "100 records
shown" to show the total number of records returned by the view (or the
search) query, e.g. records 201-300 of 2,456.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most ...Format Chart based on data
I have built a chart from a simple spreadsheet of data. Here is an example
of the data:
I want the chart (bar style) to show each bar in Blue that corresponds to a
value greater than 13. All other bars (<13) should be red.
Is that possible?
Also, I want to draw a line across the chart at 13 to show the "benchmark".
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTe...Formula Help #26
How do I enter a formula that counts based on two different criteria. I
column A I have names and in column B I have dates or blank cells.
need a formula that counts if there is a date in a specified cell i
The formula I have now is
What would I put in the ????? to make it count if the cell has a date
Message posted from http://www.ExcelForum.com
The only thing I can think of would be a two step process. In C1, us
the formula = if(and(isnumber(B1),A1="Scratch"),1,0), and so on dow
Th...Footnote in a Formula Cell
I am wondering if there is a way to add a footnote to a cell tha
contains a formula. Everytime I try it gives me an error. I canno
change the cell to a value beause the forumla in that cell is linked t
another workbook. Please HEEELP
Message posted from http://www.ExcelForum.com
Learned this from MVP Debra Dalgleish:
>I am wondering if there is a way to add a footnote to a
>contains a formula. Everytime I try it gives me an
error. I cannot
>change the cell to a value beause the foru...Can you have fixed cell reference when dragging/copying formulae?
When you drag or copy a formula which refers to another cell, then that
cell's reference is updated when the formula is dragged or copied across a
spreadsheet. Is it possible to 'fix' the reference of the outside cell such
that the formula refers to a fixed cell irrespective of where the formula is
For example a formual in cell B2 containing a reference to cell A1 will
always refer to the cell immediately above and to the left when copied
elsewhere. Is it possible for the formula to be forced to refer to cell A1
irrespective of where it is copied?
You can use $a$...Chart gridlines over an area graph
I have an excel chart with an area graph. When I add gridlines to the chart
the lines do not show over the colored area portion of the graph. i found a
button for droplines which added the Y axis lines, but have found nothing
that will show the X axis lines through the graph.
Any ideas are greatly appreciated.
Have a GREAT day!
You will need to draw the gridlines yourself using additional data series.
For horizontal gridlines you could use a series for each line, changing
the y value for each series.
Or a single series plotted as xyscatter. The x value woul...Non-number value in Charts
I am trying to make a non-number value show up in a column chart. The chart
is automatically showing any non-number value as zero. This is fine, however
I would like the Display Data Labels to show the value that is displayed in
How do I do this?
I think you're actually wanting to display either series name, or category
name. If you just want custom labels on individual data points/columns,
you'll need to manually edit the labels yourself by selecting the value, then
clicking again (aka, a slow double click).
*Remember to click "yes&qu...excel charts #4
I have 2 sets of data for times. I can plot the chart data onto an
excel bar chart and see the results, no problem. However I'd like to
compare the 2 sets of times. i.e. I'd like to have two bars, side by
side for each of the times. i.e.
Right now I can plot all the A1,B1, ... N1 values but I don't know how
to put the results for A2, B2, ... N2 beside each of those for the A1,
Am I right in thinking I just have to change the data range? Or do I
need somethign else? i can use the wizard or whatever it takes to
redraw th...Return a name in the same row
I have a 2 sheet spreadsheet. I want a formula to match a cell in column A of
sheet 1 to a matching cell in column a of sheet 2 and return a name form the
same matching row in column C of sheet 2. I have searched for this formula
and cannot find it. Please help.
CustNum ShopNum LastName
1 5 Name one
2 5 Name two
5 53 Name three
7 57 Name four
8 8 Name five
9 9 Name six
10 10 Name seven
11 11 Name eight
12 340 Name nine
13 12 Name ten
14 13 Name eleven...how can I fill labels in a chart, so that the grid lines don't in.
How can I fill labels in a chart, so that the grid lines of that chart do
not interfere with the text in those labels?
Double click the data labels and set the Patterns Area colour to be the
same as the plotarea colour.
> How can I fill labels in a chart, so that the grid lines of that chart do
> not interfere with the text in those labels?
Andy Pope, Microsoft MVP - Excel
Or you could double click the gridlines and change their color to the
faintest gray in the palette (you've alr...copy formula from worksheet to worksheet
When I copy formulas from one worksheet to another the
pasted formula includes the path and file name of the
worksheet it was copied from.
At times I can edit it out, but sometimes the formulas
get to long and are cut.
How can I copy and paste without the path and file name
in the formula.
Thanks for any help
Replace "=" with " =", which converts formulas to text.
copy and paste the cells
Use Edit/Replace then to replace " =" with "=" To make them formulas again
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 S...Is there a way to create a standard OHLC bar chart in Excel?
Does anyone know if it is possible to create a standard open high low
close bar chart in Excel? I don't want the candlestick form they have but
rather a format similar to the high-low-close included in Excel that also
uses the open price. I want the bars to look something like:
Thank you in advance for your help,
wrote in message news:A4A48B44-A8...no shipping for zero tendered
When we processing a transection that the tender is zero (like even
exchange) the invoice will not show up in the EDIT SHIPPING window, so we
can process shipping for this items?? Any solutions??
...Adding Formulas to Subtotal rows
I have a large amount of data on a spread sheet which has been sorted and subtotaled (using the “Subtotal” function on the data menu). On each subtotal row I would like to have formulas that use the results of that row’s subtotals (ie divide the value of one subtotal by the value of another subtotal on the same row). At the moment I am creating the formulas on one subtotal row and then copying them to each of the remaining subtotal rows – one row at a time. This seems to be cumbersome – is there a faster way?
one way: use a formula like the following
=IF(ISNUMBER(FIND("Tota...Zero Values on a Pie Chart
Hi Everyone! I am doing a pie chart and I can't seem to get the chart to NOT
display "zero" values on the pie. I have tried everything and still cannot
get it to not display it. I went to 'Tools' then 'Options' then to 'Chart',
where it says "Plot empty cells as:" it is gray and I am not able to select
anything. It has already pre-selected "Not plotted (leave gaps)". I don't
know if this is why it keeps showing them on the pie or not? The "Plot
visible cells only" is checked. Can anyone help me?
Also is there a way ...I need help with a table formula/mass editting
I have a spreadsheet (.csv) with 1600 products/rows and several columns. One
of the columns that has the product descriptions also contains 1600 rows. In
each row under that 'description' column is a formula/code to input a
chart/table with product details to our web site in a chart form.
I have many new products that I need to place on the web site with this
layout. I need to know how to optimize that particular column to add
specific details to the chart formula for the 1600 products. For instance, I
need the chart to be the same on each product but with different ...Im wanting to down load a file where i can go in and make my own .
Im wanting to know what file i can download so i can make my own business
There are a multitude of templates within Publisher.
Always post what version you are using.
Hey David, missed you... Everyone said you abandoned us to go to Serif. How are
things down under? I hear the Aussie PM is still on Bush's side...
Mary Sauer MS MVP
"��MS-Publisher��" <firstname.lastname@example.org> wrote in message
> There are a multitude of templates wit...Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard
Yes. You must make sure to change the site when performing a Return
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users
Get our Free Tips and Tricks Newsletter and check out our books at
"Everton Raymond" wrote:
> Is it possible to have a different default Site ID for Returns than Standard
Sorry, I did not see the word "default" in your post until after I r...Automatic Updating Charts
Just wondering if I could automatically change a chart.
For example I do a number of chart for every month, obviously days of the
months changes so on a month where there are 31st, there isnt a problem. The
problem is when i have a month which only has 30 or 28 days. This leaves the
last 3, 2 columns zero but are still on the chart but are shown as zero.
Does anyone know how to automatically adjust the source data? perhaps with
an if statement?
tushar mehta and Jon peltier have excellent solution on this. you have to
make the source as dynamic array
see their webpages by sear...How do you set up a formula ( as for a check book.)
How do you set up a formula ( as for a check book.?
You will have to give more detail on what you want to do? Do you want to
create a payment register? Do you wnat to create a bank register, where you
can record both income and expenditure?
email@example.com_2nd_at. Stilfontein, Northwest, South Africa
> How do you set up a formula ( as for a check book.?
> How do you set up a formula ( as fo...Can you have a static file location in a formula?
I would like to have a static file location that does not change if
email the workbook to somebody. Is this possible?
Here is an example of what I would like to do.
=IF(L8=TRUE,'C:\Documents\Football\Week 3\[Pool Score Sheet.xls]Scorin
Tab'!$B3,IF('C:\Documents\Football\Week 3\[Pool Score Sheet.xls]Scorin
I would like the C: Documents ... to never change. I will be emailin
this sheet so I want to formula to refer to my computer and not th
recipent. Please help
How can I pull a particular cell on each sheet onto a completely separate
worksheet. ex. Sheet1c4, sheet2c4, without having to type each particular
sheet name in?
In the target cell, type =, then click the other tab and then cell in that
sheet, and Excel fills it in for you.
(remove nothere from the email address if mailing direct)
"Todd Nelson" <ToddNelson@discussions.microsoft.com> wrote in message
> How can I pull a particular cell on each sheet onto a completely separate
> worksheet. ex. She...