Bolding/etc based on formula results...
I want to insert a formula into a cell, whereby if the results were
outside a certain range, the results would be bolded, and the cell
possibly infilled with a different background colour.
If the results of the formula fell within the certain range, then the
results would appear as normal type, and the background colour would
be the same as the rest of the spreadsheet.
Any pointers on how I can do this?
A formula cannot change a cell's format
But Format | Conditional Formatting can do just what you want.
Have a look at it, experiment, then return with questions...Formula Problem?
I am using Excel 2000 with Windows XP.
I am having a problem. I am on Sheet 2 of my workbook.
I have SSN on a sheet named Employees in the same workbook.
I need to take the numbers on the Employees Sheet and transfer it to the
I know how to do this. It just won't work.
This is a copy of my formula. =SUM(Employees!C3)
This should take the SSN that is in the C3 cell on the employees sheet and
place it at the cell where the formula is typed.
When I put this formula in the cell I am getting just a "0".
Niek Otten...Assigning Calculated value to main report from subreport
I have a report with a subreport in it. I am trying to sum up the values of a
txt-box [Extended Price] in the subreport and put the value in a txt-box,
"TotalAmountIS" on the main report. This is what I did:
1.) I created a txtbox in the footer of the subreport and called it
2.) I assigned "=sum([Extended Price])" to the control source of
3.) I created a txtbox on the main report
4.) I then assigned "=Nz([subreportname]![TotalAmount],0)" to the control
source of TotalAmountIS
This was the same way I did it on a form, with ...Filling bound controls in a form with calculated results
I have a form that a user may partially fill with text and numbers that
could then be used to calculate numerical results that could fill in other
blank fields in the same form. My idea would be to have a un-bound button in
the upper title bar of the form that the user could click after filling in
some of the initial fields, and this action would calculate numerical
results via VBA functions or procedures, the results then being used to fill
in multiple field values I know how to write the VB functions/procedures to
calculate the results, but I'm not sure where to use the
functions...Formula for adding up columns, i.e., B1:B10 + D1:D10 + F1:F10 + H1:H10 + J1:J10
I've never figured this one out. Went and googled yet again but I'm
obviously not googling for the right thing. Same as with the help file.
What is the type of formula we use for adding up columns or rows, esp. if
they're broken up by rows/columns in between?
"StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message
> I've never figured this one out. Went and googled yet again but I'm
> obviously not googling for the right t...How to get rid of "A formula in this worksheet contains one or more invalid references"
Frequently I'll delete some cells in an Excel 2007 worksheet and from
then on get the message "A formula in this worksheet contains one or
more invalid references." whenever I try to do anything in the
worksheet. There are no invalid references, as I have verified.
Possibly there were some temporarily while Excel was in the process of
deleting the cells. If I save the file, exit Excel, then reopen the
workbook, Excel changes its mind and decides there aren't any invalid
references after all (at least the message goes away). Is there any
way to get Excel to figure t...how do you delete cells when they are connected with a formula
All I know how to do is enter info into a cell. This is a list of numbered
employees. Usually, I would just seleted the cell and delete, but I get
###error. The formula is =+sum (A73+1) Please help!!!
I believe you are seeing ### because the cell is not large enough to
display the #REF! error. When you delete the cell (A73), either by
right clicking and seleting delete or by using the Edit function, the
formula no longer knows what cell to reference, and all the other cells
below the one with the error have all adjusted accordingly (in this
case, up one). If you look below the cell w...Can i use conditional formating on a cell when it contains a formula? #2
sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262
View this thread: http://www.excelforum.com/showthread.php?threadid=26784
the reason for this is that thie formula returns a string and not a
numeric value. Change the formula to:
Change your Conditional formula to:
=&quo...help with distribution formula!
i need to make a distribution table. i know that i use the -normdist-
function, i just can't figure out what to put in each argument.
my data is 10 questions, and each question has 5 answers to choose from
(multiple choice). Assuming that im making complete random guesses, i
need to find the probability of getting 1 question correct, 2
questions, 3, 4, and so on until i get the prob. of getting all 10
correct. what would i plug into each argument?
standard Dev -
i'm not specifically asking for the answers, but more for help on
figuring out which goes into...Vlookup Formula #3
column J has dates (days of each month). Column K has numbers. the whole
rang is J7:K38.
E12 has a date and F12 has a number.
In cell G12, I need to:
=F12*K? (that has the corresponding date as in cell E12)
ie. if E12 has date 14/10/07 then look for the same date in range J7:J39 and
take the corresponding number in range K7:K39 and multiply it with the value
in f12 and put the answer in cell G12. All in the same s
Hope to have an answer!!
if you indent to copy the formula down the column
Be...Formulas are inactive
I have completed a five sheet program for monitoring Mutual Fund Data. All of the formulas acted normally until I completed a sixth sheet to summerize the data. The formulas on the sixth sheet link back to the other five sheets. Upon completetion of the sixth sheet none of the formulas in the preceding five sheets will work. Help
Check out Tools>Options>Calculation. Is is set to "manual" or "Automatic"?
Gord Dibben Excel MVP
On Sun, 14 Dec 2003 10:51:10 -0800, Pete S <firstname.lastname@example.org> wrote:
>I have completed a five sheet program for monitorin...how do I calculate times and get as a result numbers
I got a Spreedsheet that I whant to use as a time sheet, this is so far what
I got done wrong what I want to get is right
a1= 10:00am time in a1= 10:00am
b1= 2:00pm time out (lunch) b1= 2:00pm
c1= 2:30pm time in (lunch) c1= 2:30pm
d1= 8:00pm time out d1= 8:00pm
e1= 8:30 total e1= 8.5
the answer has to be a number if the time is #:0<= #:14 = #.0
#:15<=#:29 = #.25
...VLOOKUP not working correctly!
I have been using VLOOKUP on a few reports recently and it has been
working great, however, cannot seem to get it working on a particular
field. I am linking from a master file and linking to current
When you select the job number in the workbook it populates all the
address fields etc fine from the master file, and also copies the name
ofr the Project Manager in (Cell Ref: D25), however from here I have a
table that details the mobile numbers and emails address of each on
seperate tab of current workbook called "Internal Contacts" thus:
=VLOOKUP(D25,'Internal Con...[b]calculating Month Average For Exch Rates From Data Array
need Yr help in formula creating. We have exchange rate calendar (USD
to Ukranian Hryvna) by every banking day in the year. Data organized as
following: col A - dates DD/MM/YYYY, col B - appropriate exchange rate,
for instanse - 5,05. I need to have in col C average month exchange
rate. So do i need to recognize that date belongs to the same month in
the same year and then to include appropriate rate to array for
calculating an average? that's my idea... Please comment
thanx in advance
------------------------------------------------------------------------...Excel Formula #12
I am trying to have text and a formula reside in teh same box with the
date in the format shown (23-May-04). Here is as far as I get...
="Todays date is "&TODAY()
and the results I get is
Todays date is 38131
Rick, here you go:
="Today's date is "& TEXT(TODAY(),"dd-mmm-yy")
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com
"Rick" <email@example.com> wrote in message
> I am trying to have text and a formul...RE: Install the correction update from the Microsoft
Content-Type: multipart/related; boundary="lgtoqnldawbpqmrot";
Content-Type: multipart/alternative; boundary="oiaosghhnqjyoimw"
this is the latest version of security update, the
"September 2003, Cumulative Patch" update which eliminates
all known security vulnerabilities affecting
MS Internet Explorer, MS Outlook and MS Outlook Express
as well as three newly discovered vulnerabilit...Need formula to calculate time difference
I am certain there is a really simple way of doing this and I am just too
stupid to figure it out...
I just want to have Row 1 Column B show a start time I manually enter (say
9:15PM), then be able to enter 10 (ten minutes) in row 2 column A to show a
value of 9:25PM in row 2 column B.
Row 2 Column B has time value (9:20PM)
Row 2 Column A has minutes allowed (20)
In other words, add B2 and A3 and show them as a new time in B3.
Hi, if you type just 10 on a2 the formula should be
if you type 00:00
is just b1+a2
regards from Brazil
"mar...Adding TEXT in front of a DATE formula
Is it possible to add text in front?
I am not getting there with this setup.
Can someone help?
="Date Printed: " & TODAY()
Note this will show up as something like:
Date Printed: 37903
Probably not what you wanted. But, you can use the
TEXT function to format the date...
="Date Printed: " & TEXT(TODAY(), "MMMM DD, YYYY")
Date Printed: October 9, 2003
="Date Printed: " & TEXT(TODAY(), "MM/DD/YYYY")
Date Printed: 10/09/2003
"Ray...Calculating for personal car mileage?
I want to create a formula that will calculate the amount of dollar due
employee if he uses his personal car for business purposes. I cannot figure
out how to avoid to overwrite the formula I created to perform that function.
For example, my formula is "=A1*0.485", but the formula is over written every
time I enter 10 (miles) in A1. How do I solve this problem? Please help!!!
> I want to create a formula that will calculate the amount of dollar due
> employee if he uses his personal car for business purposes. I cannot figure
> out how to avoid to overwrit...Multiplication formulas....
A 2 part question here. Im completely new to excel so I apologize for
my lack of terminology....
1) Is it possible to multiply all the numbers in a column by a single
fixed number? For example, the column is a list of prices in US
currency but, I would like to convert them all to Canadian currency.
Can I do this without having to manually do it to each cell? Also, can
I have the answer come up in a different column?
2) Is there a way to multiply 1 set of numbers by numbers in a seperate
column. To continue with the previous example, can the numbers in the
*'prices'* column be...Excel Formula depending on number in cell..
I have a question that I thought some of you could answer:
I have a number in column D and based on if it is $0-$100 I want i
multiplied by 0.10 and then rounded up to .95.
So, if it is 20.49 + 10% = $22.54 and then rounded up to $22.95
Now this is where it gets tricky..
If it is between 100.01 to 200.00 + 7% and then rounded up to a .95.
So for example 129.44 + 7% = $9.08 = $138.50 then rounded up to .95
I need to do this for a range of numbers like 200.00 to 300.00 an
300.00 to 500.00 etc, etc..
Could someone point me in the right direction?
I appreciate a...VBA code to find all formula cells with a variable and a constant
Using XL 2003 & 97
Not yet successful at trying to write VBA code to find all formula cells
with a variable AND a constant.
Also, I would like each identified cell to background-fill with yellow.
Example: (Cells A2 & A3 are background-filled yellow)
A1 = B1+C1 (Not selected - background no-fill)
A2 = B2+10000+C2 (Selected - background - yellow)
A3 = B1+C1+15000 (Selected - background - yellow)
...Excel 2007 formula, multiple reference range
Help. I am collating data from 6 facilities. I have one worksheet with 7 pages. The first is a summary of all data. The other 6 have individual data for each facility. On the summary page, I need a total number, pulled from all 6 facilities. On the facility detail pages, I have summed the occurrences in that facility. Now, I need a formula that will let me get a grand total. For example:
pg 1 B5 needs to have the total from:
pg2 K5:K100 if J5:J100 = pg1A5
pg3 K5:K100 if J5:J100 = pg1A5
pg4 K5:K100 if J5:J100 = pg1A5
pg 6 SAA
pg 7 SAA
The reason w...Count formulas
I am trying to do a set of 3 counts between 1-100. Under
80 Between 80-90 and 90+.
I have 2 formulas that are working and can't seem to
figure the 3rd, could anyone help?
1st =COUNTIF(G4:K22,">89.99") 2nd =COUNTIF
(G4:K22,"<79.99") It is the middle one that I am not able
to figure out.
count all -those two or
"Barb" <firstname.lastname@example.org> wrote in message
> I am trying to do a set of...How to eliminate zero values on graphs when formula calculates 0?
I'm setting up a graph of data that is calculated with an "if" function over
a series of days of input data including future days. When the input data
cell is blank I would like the calculated value to not show a zero value on
the graph. If the cell were blank, the plot would not show a zero value.
The problem is that at the end of the line graph the line always falls off to
"sjgolfer" <email@example.com> wrote in message