keeping values

I am sure that this question has been asked and answered. I am creating a 
worksheet for my class.  I have a value in cell b2( I want use this value 
thru out the sheet). Let say   b2 = 10

       b     c     d    
3    5      6     8
4    3      7     1

I want to subtract each value from b2(10). How can I do this without doing 
it manually?
0
Utf
4/1/2010 9:06:04 PM
excel.misc 78881 articles. 3 followers. Follow

2 Replies
393 Views

Similar Articles

[PageSpeed] 17

On Thu, 1 Apr 2010 14:06:04 -0700, AFL-math
<AFL-math@discussions.microsoft.com> wrote:

>I am sure that this question has been asked and answered. I am creating a 
>worksheet for my class.  I have a value in cell b2( I want use this value 
>thru out the sheet). Let say   b2 = 10
>
>       b     c     d    
>3    5      6     8
>4    3      7     1
>
>I want to subtract each value from b2(10). How can I do this without doing 
>it manually?

Does that mean that you want the following end result
(if not, explain what result you do expect).

      b     c      d
3    5     4      2
4    7      3     9

This may be accomplished by the following command sequence

Put -1 in any spare cell, say F10
Select cell F10 (that has the value -1)
Copy
Select cells B3:D4
Paste Special -> Operation = Multiply
Select cell B2 (that has the value 10)
Copy
Select cells B3:D4
Paste Special -> Operation = Add
Clear cell F10 (the spare cell)

Hope this helps / Lars-�ke
0
Lars
4/1/2010 9:44:07 PM
Refer to the cell via its absolute address: $B$2.  That way as you 
'drag/fill' formulas using it around on the sheet, it won't change.

To subtract a value from it
=$B$2 - 4
or even another cell reference
=$B$2 - D3


"AFL-math" wrote:

> I am sure that this question has been asked and answered. I am creating a 
> worksheet for my class.  I have a value in cell b2( I want use this value 
> thru out the sheet). Let say   b2 = 10
> 
>        b     c     d    
> 3    5      6     8
> 4    3      7     1
> 
> I want to subtract each value from b2(10). How can I do this without doing 
> it manually?
0
Utf
4/1/2010 9:46:42 PM
Reply:

Similar Artilces:

Formula to Value
Hi there I have just updated to from 2000 to 2003, and cannot find a toolbar button for formula to value. I can copy and paste special in the same cell, but this is mildly irritating. Am I being dumb? TGHCOGO --- Message posted from http://www.ExcelForum.com/ I don't think it has ever been there without help at least. Do as follows: click view>toolbars>customize, select commands, select edit in the left pane and scroll down until you see something that looks like an old time almanac date that you would tear off every day |12| it has a 12 in it, paste values is the name, now s...

Add pecent and value on the same graph
I would like to add the percent and the value and display it within a pie chart. Thanks for your help. You can calculate the percentages on the worksheet, and use a formula to create the labels. For example, with the following data in in A1:B3 -- Jan 25 Feb 25 Mar 50 Enter the following formula in cell C1, and copy down to C3: =B1/SUM($B$1:$B$3) To calculate the text for the pie chart labels, enter the following formula in cell D1, and copy down to D3: =B1 & " " & TEXT(C1,"0%") When you create the pie chart, in step 2 of the Chart Wizard, click ...

Outlook keeps prompting for logon..
User is on Outlook 2k3, latest service packs, updates, etc. Mailbox lives on Exchange 2007 (SP1). Everytime the user starts Outlook they're prompted to logon. I've disabled the logon prompt at startup option (and enabled it and then disabled it again), but it's still happening. I've tried it with cached mode on and cached mode off. I've tried it with offline mode enabled and disabled. I've tried deleting his profile and recreating it. I've tried switching to an RPC/HTTPS connection. Finally, i completely uninstalled Outlook 2k3 and installed Outlook 2k7. Not...

Help Old addresses keep coming up
When I go to send an email to some people their old email addresses keep popping up together with their current ones. I do not have them listed in contacts or in an address book. It is a minor annoyance. I am using Outlook 2003. It is a minor annoyance. How do I delete these old addresses? Where they show up is where I am entering the e-mail address on the item being e-mailed. Thanks You did not specify how you were entering the addresses. Presumably you are using autocompletion and they are...

Find highest value in each row.
Hi, I have a problem trying to find the highest value for each row. I have a query, which adds up scores. Its based on a table something like this orgID Score1 Score2 Score3 Score4 Score5 Score6 1 10 1 5 2 11 20 2 1 4 2 12 11 24 1 10 7 15 12 1 2 and so on..... I have query that basicly adds up some of the scores into groups. So its like: orgID ScoreSet1:[Score1]+[Score2]+[Score3] ScoreSet2:[Score4]+[Score5]+ [Score6] 1 16 ...

Use a cell's value as row number for another cell in Excel
Excel XP: I'm trying to use the values in one row of cells (D1:F1) as the reference for the row value for a different series of cells (D2:F4). For example: Column A B C D E F Row 1 1000 30 170 1 2 3 2 1500 60 190 3 1200 20 175 I want to get the following cells to equal: D2=1000 (In other words: D2 = A1); D3=30 (In other words: F2 = B1); E2=1500 (In other words: E2 = A2); E3=60 (In other words: F2 = B2); F2=1200 (In other words: F2 = A3); F3=20 ...

how to enter a value, and have it SUMMED with the existing cell co
An Excel cell contains, say= 4. How do you enter, say=2, and have the result = 6? (not COPYING and Paste As ...) ...

Confirmation of receipt keeps trying to send and fails
Help! I received an email message that requested a confirmation that it had been received. I told it to send confirmation, but it failed when attemtping to send it and produced an error. I tried deleting the email message and everything in the draft items and Outbox, but every time I switch on Outlook the darn thing tries to send this email confirmation. It seems there is nothing I can do to stop it, even though I cannot see it! I have tried uninstalling Office 2003 and reinstalling it, but all of my settings are remembered as are all the old emails. I just cannot seem to get rid of it! ...

Keep Lines Together; Keep with Next
I'm working in Vista. When I select lines and click on "Keep Lines Together," the lines do not stay together. They stay together if I also click "Keep with Next," but I'm working on a booklet in which the pages are 1/4 of a letter-sized page, and a lot of space (4 lines) is left at the bottom and wasted if I also click "Keep with Next." Why won't "Keep Lines Together" keep the lines together? The project is a small, bound country club booklet, with names, addresses, phones, etc. Each entry might be 2, 3, or 4 lines. Any h...

Returning a lookup value from multiple sources
I have 4 worksheets - WIP, Orders, Invoicing, Costs. In the WIP sheet, I summarise data from Invoicing & Costs by Orde number. Some of the Orders will be invoiced, some have costs, som both. Some order numbers will appear more than once in each sheet, som not in either. Summarising the data is easy, once I have each order using SUMIF, bu how do I get the unique order numbers from the other sheets into th WIP sheet in the first place?? This is driving me mad!! -- Message posted from http://www.ExcelForum.com Hi I assume that your 'Orders' sheet should store all order numbers....

why do my pictures keep disappearing?
Although I have checked the box in 'pictures' any form of graphic or word art that I have inserted into my publication keeps disappearing! If I click in the general direction of the insertion the bounding dots appear, but no picture Are you sure you have view, pictures, detailed display checked? If so try updating your graphics driver... -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "JG" <JG@discussions.microsoft.com> wrote in message news:57E101AC-A758-400A-9269-3B68B754CA0E@microsoft.com... > Altho...

update subform from value in another form
I have a mainform and two subforms liked through member number. I want to enter data in the subform for every quarter for each member. I have an unbound combo box in the main form to select the quarter. How do I store the quarter information for each member in the subforms based on the combo box. I don’t want to store the quarter information in the main form. SeekAns - It sounds like you are adding new records in the subform. If so, set the default value of the control in the subform to be the value selected in the combo box on the main form. -- Daryl S "See...

Absolute Value Trendline
Hi, I'm working on a Mechanical Engineering lab with a plot of data in the form of an absolute value function. Is there any solution to allow Excel to plot a line of best fit as an absolute value function? In a range of your worksheet, convert the values into absolute values using the ABS() worksheet function. For example, if the values are in column B, starting in B2, in C2 enter =ABS(B2) and fill this formula down as far as you need. Plot this data and add a trendline to this series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Cust...

Outlook 2003 keeps asking for password for POP3 account
I am running WinXP Pro and Office 2003. My Outlook 2003 keeps popping up intermittently a pop-up box requesting me to re-enter the POP3 account password. Now, there is nothing wrong with the password already entered (check box to save password), so all i am having to do is click OK, but it is really annoying. Does anyone know what i can do to stop Outlook from doing this? Thanks for your help. ...

how can I keep a tab in place?
I have workbooks with multiple worksheets. I would like to keep the summary tab in a fixed (first) position so that the user can easily, quickly find it without using the l<, <, >, >l (arrow) tabs. I use hyperlinks to get from the summary tab to the other tabs, and I know I can hyperlink tab back... but I'd rather not use the hyperlinks too much. Tools - Protection - Protect workbook (for structure) Or, create a custom button/macro that navigates to the summary sheet. -- Best Regards, Luke M "missy8892" <missy8892@discussions.microsoft.com>...

default value for one field based on another
I have a form with two date fields on it - Service Date and Posted Date. Since in most cases the Service Date will be the same as the Posted Date, I have the default value of Posted Date field set to the value of the Service Date field by using the following expression in the default value property: =[ServiceDate]. It's not working though and I'm not sure why. Try this in the AfterUpdate event of the first date: Private Sub Date1_AfterUpdate() If Nz(Me!Date1, "") <> "" And Nz(Me!Date2, "") = "" Then Me!Date2 = Me!...

Total Value and Account Value do not match??
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I am using Microsoft Money 2004 Standard. I just set up a new account to keep track of mutual funds however even with just one transaction listed I get a weird total figure. Here are the figures: Shares: 1593.298 Price: $10.06 Total Value: $16028.58 Except Money says the "Account Value' is $16140.11 Where does the difference come from? How do I get the numbers to match up? Thanks - -- Due to large amount of spam mail as well as personal security, please respond directly to this newsgroup. Encrypted Messages, using PGP can be poste...

How do I find value on a graph between data points?
I have a set of data points, ie. x = 1,2,3,4,5,6 corresponding with y=4,17,18,22,15,7 I want a cell that will work out the expected value for y for any value (ie. x=3.3, y=?) when the x value is provided to it from another cell. I want to be able to change that input cell and automatically recalculate the new value of y. I need some kind of routines that finds the data points either side of the required value and interpolate between them. How do I make this routine and carry out the interpolation? Although not designed for this use, the FORECAST function will do an excellent job of...

How do I keep the home tool bar on top
An hour ago the home tool bar stoped being displayed. I can restore it but every time i hit a key the tool bar disapers. How do I keep the tool bar on top while I am typing. You've minimized the Ribbon. Double-click any tab on the Ribbon to restore it. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "RobertTaylor" <RobertTaylor@discussions.microsoft.com> wrote in message news:081A95C4-76B9-47E2-A4E6-83A080233AD0@microsoft.com... > An hour ago the home tool bar stoped being displayed. I can rest...

How to keep userform always on the top of all windows application
Hi All!! Is there some that when I execute an excel user form, it *remains o the top * of all the windows applications that are currently running until & unless I, manually, minimize or close it. Regards, Jat -- JAtz_DA_WA ----------------------------------------------------------------------- JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2668 View this thread: http://www.excelforum.com/showthread.php?threadid=40059 You may get an answer to your question--but it'll involve both making the userform always on top plus adding those minim...

Keep it alive
Hello! Is it possible to declare an Access.Application object in a module of another db (let's call it db1), display the application window to the user interface and keep it alive even when db1 is closed? Because the database object is closed as soon as the memory used by db1 is released.. Thanks for any suggestion! Answered in another group where you posted this. ...

how to keep text with next paragraph
"Church Mouse" wrote: > What is the problem? Version? ...

deposit to keep??
When you cancel a layaway and rms ask you if you want deposit to keep and you say ok, what happens to the money and where does that money go? and is there a way to turn off that button? ...

Permission to fill in cell when another cell has a value
How to set that in a cell B1 it is allowed to fill in data only when Cell A1 has any or has a certain value . I guessed via Data Validation, but failed. Bart Excel 2003 Select cell B1, and choose Data>Validation For Allow, choose Custom In the Formula box, enter: =A1<>"" Remove the check mark from Ignore blank Click OK AA Arens wrote: > How to set that in a cell B1 it is allowed to fill in data only when > Cell A1 has any or has a certain value . I guessed via Data Validation, > but failed. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.ht...

keep or not keep SMTP log files?
Hi, I'm curious about what do you do with your SMTP lof files? How long do you keep your smtp lof files? Save them onto a tape media or external harddrive? Do you delete them after 6 months? Or don't keep them at all? I'm trying to get a general idea what administrators do to their smtp log files. Thanks and appreciate it. Ricky On Wed, 25 Oct 2006 13:01:04 -0700, Ricky T <RickyT@discussions.microsoft.com> wrote: >Hi, > >I'm curious about what do you do with your SMTP lof files? How long do you >keep your smtp lof files? Save them onto a tape medi...