Help with displaying formulas in charts
I am looking for a way to display zero values in my charts as gaps. Right
now I there is a big slant in my chart that I am trying to get rid of. The
range I am using has formular in them, so even when the cells evaluate to
blanks, they are displayed as zeros on the chart.
Any help will be greatly apprecaited.
Two possibilities I can think of"
1) Add an IF statement, to your formula, in order to present N/A instead of 0
[ use the function NA() ]
Something like that:
2) Hide all the rows where the cell...Charts
Here is my formula:
=IF(ISBLANK('Detail Qry'!E25),NA(),'Detail Qry'!E25)
It returns a #N/A like it is supposed to be it is still being charted on my
chart. I have tried ISERROR and ISERR but they don't work either.
I am actually using this formula to pull in a row of dates and then column A
is a work center. So really what i have is a crosstab.
Column A Column B Column C
L1 390 cs 502 cs
B4 425 cs 618 cs
When i run the query from the referenced sheet i will get...Formula help... #3
In column A there are cells with team names in them like the following:
I would like column B to list all the team names without the @ symbol and
column H to list all the teams with the @ symbol. For the example I would
like column B to have
(remove nothere from the email address if mailing direct)
"Jambruins" <Jambruins@discussions.microsoft.com> wrote in message
I have lists of insurance names followed by their types in once cell
i would like to have a column that would show me the type so i can sort by
type (my report doesnt have 'type' column)
i need the formula to say
"if cell contains 'WC' then the result equal 1, if the cell contains 'grp'
then the result would equal 2, if the cell contains 'PIP' then the result
would equal 3"
i cannot figure out how to write that kind of formula
thank you MS community
Since both Name & Type are in o...Count Formula #4
I have a spreadsheet with three tabs. They all have
people requesting time off...first sheet is for people
requesting more than a week off - 2nd for 1 week at a
time - 3rd for people just wanting 1 day off. The sheets
have their name then 2 columns one for days off starting
and the other for days off finishing (With the exception
of people just wanting one day off)
I would like to have another sheet counting the number of
people off on a daily basis. It is easy enough
using "counif" for the people asking for 1 day off...but I
can't seem to easily break out the weekly...Referencing Row Numbers in Formulas
I have a spreadsheet where I have two columns of numbers
Column C Column F
Start Row # End Row #
I would like to define a sumproduct using these rows as guides i
defining the range. I thought maybe I could use the indirect key t
reference these cells as the row number, but it's not working as
...Formula on Actual Vs Plan
Could some help me on this formula?
I need to complete a task say from 1-Jun-05 to 30-Jun-05 i.e 30 days,
but however on a given day when I analyse the Project schedule (say
today), I find out that on the 9th day i.e 9-Jun-05 on actual progress
I have completed only 4% of the Job, Hence what should be my forecasted
days to complete the job a 100% in actual, now putting it in a
100% = 30days (Plan)
4% = 9days (Actual & when the plan is 30%)
Hence 96% = ? (How may days in Forecast)
I want the formula to be dynam...if(isna(match formula
The following formula will not work and for the life of me I can not
figure out why:
In cell a1 is the text "All Players". From cells a2-a55, I have the
following data: player1, player2, player3...player54. In cell b1 is
the text "Available players". From cells b2-b55, I have the above
formula so the default values are the same values as column a. From
cells d2-d7 is the text team 1, team 2...team 6. The values in cell
range e2:m7 will be populated by a dropdown box which includes all the
data from cells a2:a...Newbie Q
I am wondering if there is a way to prevent a formula to be updated after an
As an example, let's say that I have a cell on Sheet2 that references
Let's say that I insert a row in Sheet1 above row 4. Automatically my
formula on Sheet2 is updated so that is references Sheet1!A5. Is there a way
to prevent this?
Check out Indirect function
"Atchoum" <NO_goglus_JUNK@videotron.ca> wrote in message
> I am wonderin...how to copy formula from one workbook to another
I have a blank workbook containing many formulas and many worksheets. I use
this to create a new workbook everymonth and enter data. After a while, I
found a way to improve in a subset of formulas. How can I go back and reapply
these formula to the entire workbook (including all worksheets, some of them
are identical but others are different) without doing lot of manual work?
How about the paste special? It didn't work or not likely what you wer
hideki's P...Concatenating two formulas
I have two seperate data in two seperate columns. say columns are
Now i wish to write a formula that will count the occurance of 123 with
corrosponde to ABC. say for above columns, value would be "1".
i am trying it with COUNTIF, but i am not able to concatenate two COUNTIF's.
Better to use cells to hold the criteria:
D1 = ABC
E1 = 123
"Mukesh Garg" <Mukesh ...Saving without formulas
Is it possible to save a spreedsheet with only the the cell values and not the associated formulas. Basically, what I want to do is a giant Copy PasteSpecial Values across an entire workbook
"Bob Ewers" <email@example.com> wrote in message
> Is it possible to save a spreedsheet with only the the cell values and not
the associated formulas. Basically, what I want to do is a giant Copy
PasteSpecial Values across an entire workbook.
Group the worksheets ...Conditional Formatting with Formulae
I'm quite happy getting part of a row to format as I wish by using a formula
and applying it to a range but I want to use this about 50 times in a single
spreadsheet and I cant stop the range being absolute so when I drag/fill I
don't get the effect I want. Is there way to fill down conditional formats
which have been generated using a formula?
Not with FrontPage.
Try asking in a newsgroup or forum that deals
Microsoft MVP (Expression Web)
RODXL presented the following e...Update formulae
Financial planning spreadsheet containing many long interdependent formula
needs to be updated from 04 to 05. Is there some quick way of editing this
The answer is "Probably yes", but will you please provide an example of
a formula? If it was a simple search and replace you probably would
have done it already. Is it a matter of changing a cell reference from
'SheetName'!A1 to 'SheetName'!B1, where A1 is a 2004 total and B1 is a
...Offset formula help!!
I am trying to define ranges each of my ranges is characterised by an
identifier in this case "D1" then directly below it are property
descriptions. So my sheet looks like this:
My problem is that when i use this formula it counts all text values so
when i want to select range D1 rather than selecting D1 and the three
rows below it, it selects D1 and 8 rows below D1. Any ideas on how I
can make the formula select the range properly?
-----------------...Please help with this formula
I have a spreadsheet; 25 participants for 31 days
each participant must log a catagory (eg PC) the spread sheet count the
number of PCs for the 31 days (eg. formula (=COUNTIF(E10:AI34,"PC")).
Question how can I calculate how many participates have logged a catagory PC
within the 31 days.
One way, Put =COUNTIF(E10:AI10,"PC") in cell AJ10
And drag down to AJ34
Then in another cell of your choice put
"C Sealy" <C Sealy@discussions.microsoft.com> wrote in message
news:13350DAB-8BEC-4731-B235-B4B...copy/paste vaules, not formulas
Is there an easier way to copy and paste the values of a cell without using
notepad? When i paste directly to new column the formulas get copied--i
only want to paste the final values...
Message posted via http://www.officekb.com
Copy the cells
Right click on the target cell
Choose Paste Special
Regards Ron de Bruin
"jeremy via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote in message news:2af487a568964ca5af6a0c284eb172b0@OfficeKB.com...
> Is there an easier way to copy and paste the val...Is there an Excel formula to round a date to the end of the month
User enters a specific date, I'd like to round that entry to the end of the
With a date in A1:
step into the next month and then back one day.
Gary''s Student - gsnu201003
> User enters a specific date, I'd like to round that entry to the end of the
This requires the Analysis ToolPak add-in be installed for Excel versions
prior to Excel 2007.
A1 = some date
Format as Date
Microsoft Excel MVP
"Siralec" &...I would like some formula for students score
Happy New Year and Sawasdee from Thailand. I have a file that I would
like anyone who can help me to solve my problem. Please d/l my file and
help me please.... Thank you
Attachment filename: mark.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=404449
Message posted from http://www.ExcelForum.com/
> Happy New Year and Sawasdee from Thailand. I have a file that I would
> like anyone who can help me to solve my problem. Please d/l my file
> and help me please.... Thank you
Not saying you would purposely send a virus, ...Formula Result is different from the shown result
In the Function Arguments dialog the official Formula Result is different
(and wrong) from the result shown after "=" just under the Serial number
I am using the Year formula. Whatever formatting I use for the date neither
this formula nor Text to Columns can return the correct year.
EG: Date is 15-05-07 or 39217, Forumula Result = 1905 but under serial
number field it shows the correct result of = 2007
This question; at least to me, isn't clear. What are you entering and where
and what is the formula you are referring to?
If you enter something ...Recalculate formula
Hi; I have a very large spreadsheet; with eg. from 2-4000 rows of data for
each household in each suburb. I have separated the suburb records by writing
a macro that inserts two lines between each separate suburb (ie. between
Suburb AAA and Suburb BBB). In those two lines, I wish to calculate the Sum,
Average and Median of the data in column B (A is the suburb name). I filter
the suburb for blanks, and then paste and copy down the formula into all 3
cells of the blank rows (all formulas refer to the data in column B).
However, I have a weird problem with the formulas, in that the formul...Help, I need a formula for work.
Help, I need a formula for work.
Here is a generic version of my problem?
Leauge Team Attendance exceed 20,000?
American Yankees Yes
American Red Sox No
National Phillies Yes
National Mets Yes
National Giants Yes
National Dodgers No
American Twins No
American Angels Yes
Total would be 5 "YES'' Answers
Now the spreadsheet is FILTERED to only show "AMERICAN"
Leauge Team...Conditional Formatting formula not acceptable?
I'm trying to Conditional Format a number of cells using "Formula Is" &
"=ISODD(INT($f2))=True", ie. if the value in F2 is odd, format the data. I
keep getting the message "You may not use references to other worksheets or
workbooks for Conditional Formatting Criteria". Why am I getting this error?
I used this formula instead:
But was interested to know why my original wasn't acceptable.
=isodd() is in the analysis toolpak (tools|addins).
It's not built into excel.
Could anyone please tell me, I have developed a worksheet template were I
have sourced the data labels in my chart. Sometimes this means 0 is inserted
in the chart and I wondered if it was possible when I used the formulae below
that I could state that if the source cell equalled 0 then this should not be
Many Thanks in anticipation.
=If(' Data'!$O$32=0, "",' Data'!$O$32)
Productivity add-ins and downloadable books on VB macros for Excel
"JEM" <...Need a formula for Excel 2000..
Greetings & bless your little cottons for existing. I've never don
anything more than autosum before and now my workplace has gifted m
with a spreadsheet project that looks monumental to me. Can anyone tel
If, in Sheet 1, the cell range C2-C41 contains "Cohen" anywhere AND th
cell range R2-R41 contains "allowed" anywhere, I need to have th
number of times that these two conditions are satisfied displayed a
that number of times, in Sheet 2 at cell B2.
So far I have =(IF((C2:C41="Cohen")*(R2:R41="Allowed"),)
I can't find the command...