Help with percent formula beginner

Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary
10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be
$36.00 (percent decrease). Can you please help me out with the formula?

Also it seems like if I type in % in a cell and I delete it and type
another number and I don't want a % in there it gives it to me anyway.
Can I make this stop?

Thanks so much

Ted

0
cann (4)
11/30/2005 4:51:04 PM
excel 39879 articles. 2 followers. Follow

6 Replies
114 Views

Similar Articles

[PageSpeed] 51

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
        With Target
            .Offset(0, -1).Value = Round(.Offset(0, -1).Value * (1 -
..Value), 2)
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Ted" <cann@dca.net> wrote in message
news:1133369464.817586.209500@f14g2000cwb.googlegroups.com...
> Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary
> 10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be
> $36.00 (percent decrease). Can you please help me out with the formula?
>
> Also it seems like if I type in % in a cell and I delete it and type
> another number and I don't want a % in there it gives it to me anyway.
> Can I make this stop?
>
> Thanks so much
>
> Ted
>


0
bob.phillips1 (6510)
11/30/2005 5:18:03 PM
Thanks, so quick too. It works! Although I need $39.99 to be constant.
Like if I put 10% in it goes to $36.00. But if I put in 50% in place of
and after 10% it goes to 18 instead of $20.00. Any ideas?

Ted

Bob Phillips wrote:
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>     On Error GoTo ws_exit:
>     Application.EnableEvents = False
>     If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
>         With Target
>             .Offset(0, -1).Value = Round(.Offset(0, -1).Value * (1 -
> .Value), 2)
>         End With
>     End If
>
> ws_exit:
>     Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Ted" <cann@dca.net> wrote in message
> news:1133369464.817586.209500@f14g2000cwb.googlegroups.com...
> > Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary
> > 10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be
> > $36.00 (percent decrease). Can you please help me out with the formula?
> >
> > Also it seems like if I type in % in a cell and I delete it and type
> > another number and I don't want a % in there it gives it to me anyway.
> > Can I make this stop?
> >
> > Thanks so much
> >
> > Ted
> >

0
cann (4)
11/30/2005 6:05:26 PM
Try this variation

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
        With Target
            .Offset(0, -1).Value = 39.99* (1 - .Value), 2)
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Ted" <cann@dca.net> wrote in message
news:1133373926.128294.80720@g49g2000cwa.googlegroups.com...
> Thanks, so quick too. It works! Although I need $39.99 to be constant.
> Like if I put 10% in it goes to $36.00. But if I put in 50% in place of
> and after 10% it goes to 18 instead of $20.00. Any ideas?
>
> Ted
>
> Bob Phillips wrote:
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> >     On Error GoTo ws_exit:
> >     Application.EnableEvents = False
> >     If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
> >         With Target
> >             .Offset(0, -1).Value = Round(.Offset(0, -1).Value * (1 -
> > .Value), 2)
> >         End With
> >     End If
> >
> > ws_exit:
> >     Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Ted" <cann@dca.net> wrote in message
> > news:1133369464.817586.209500@f14g2000cwb.googlegroups.com...
> > > Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary
> > > 10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be
> > > $36.00 (percent decrease). Can you please help me out with the
formula?
> > >
> > > Also it seems like if I type in % in a cell and I delete it and type
> > > another number and I don't want a % in there it gives it to me anyway.
> > > Can I make this stop?
> > >
> > > Thanks so much
> > >
> > > Ted
> > >
>


0
bob.phillips1 (6510)
11/30/2005 7:12:35 PM
Hi, unfortunately receiving a compile error: Expected end of statement
at the , before 2).

Ted

0
cann (4)
11/30/2005 9:38:42 PM
Sorry, should have tested it.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
        With Target
            .Offset(0, -1).Value = Round(39.99 * (1 - .Value), 2)
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Ted" <cann@dca.net> wrote in message
news:1133386722.791301.179960@o13g2000cwo.googlegroups.com...
> Hi, unfortunately receiving a compile error: Expected end of statement
> at the , before 2).
>
> Ted
>


0
bob.phillips1 (6510)
11/30/2005 10:19:57 PM
Perfect! Thanks you so much. Your a life safer.

Ted

0
cann (4)
12/1/2005 5:53:01 PM
Reply:

Similar Artilces:

Please help me with this judgment
Hi all, I need this function to deal with thousands of records. Please give me a hand. Thank you very much. I would simplify my problem to the following question. Say I have a column A and B in sheet1, A1=fdhjsa, A2=rieqrew, A3=mxnvvc,..., A10=rewkjrk. Now I want to set a judgment to corresponding B cell, if "fdhjsa"(A1) exist in sheet2!$A$1:$Z$500, then B1="yes", else B1="no", if "rieqrew"(A2) exist in sheet2!$A$1:$Z$500, then B2="yes", else B2="no"... and so on. One more question, could I set B2 a color instead of a value of "...

Help with Audit, Can't assign value
I have a form which is used to input customer data and has a control which is set to record the current user using the CurrentUser() function tied to the Before Insert event of the form. The same form contain a lookup button with which I can close the form and reopen it with an openargs that causes the form to us a dataset tied to a query which requests the customer last name and returns matching records. The problem is that when I click the button to look up customers I get an error stating that "You can't assign a value to this object". In the VBE the line that I used to assig...

HELP, HELP, HELP, PLEASE HELP!!!!!!
I have an exchange server box, 5.5. with SP3. Norton Antivirus deleted the edb.log file and the information store would not restart. somehow, before i got to the server, most of the files were removed and the service restarted with a new priv.edb and pub.edb. Problem i have is this; I have two huge priv and pub files - about 1.5 gigs each - just sitting there and CEO, CIO, and CFO want all their old emails. Dude that claims he does backups, did not do backups this time for some strange reason and i have absolutely no kind of backup - just these two files. Is there any way i can w...

Form defaults
Hi, I have a button on a form that disables and rearranges controls. I think I went into design view it it pressed and that control arrangement became the default. I cannot change it back though. I tried clicking the button twice and everything goes back to how it should be in form view. Then when I go to design mode everything goes to the wrong defaults. I hope that makes sense. Thanks for your help. ...

Help with modifying a formula.
Hello, I have this bit of code that is part of a larger macro: ' Populate the Month Column with new Monts .Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol + 1)).Formula = _ "=IF(MONTH(H2)=12,CurrentPeriod(H2),IF(H2<=LastFriday(H2),CurrentPeriod(H2),NextPeriod(H2)))" .Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol + 1)).NumberFormat = "MM-YYYY" What this does is populate the column to the right of the last used column in my data spreadsheet with a formula that does some checks on the dates stor...

Help formatting please.
So I have two columns, several pages long, full of phone numbers. Some of the numbers in the columns look like this: (555) 555-5555 and about half the other ones are lacking the parenthesis. IE 555-555-5555. I want to make everything uniform by adding parenthesis to all the numbers. How do I go about doing that? Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ cruisin4burgers, Assuming that ALL of the phone numbers are either ###-###-#### OR (###) ###-####...

Report Order- Help!
Hi, I have a report using a query as the data source. The query presents the clients in numerical order, but the reports are not in numerical order. I need this for the mailing aspect. Thanks On Tue, 13 Nov 2007 12:39:02 -0800, Novice2000 wrote: > Hi, > > I have a report using a query as the data source. The query presents the > clients in numerical order, but the reports are not in numerical order. I > need this for the mailing aspect. Thanks The sort order in your query is irrelevant to the sort order in a report. To sort your report, in Report Design view click on...

I need help to create this layout
Please click on this pdf http://www.michigan.gov/documents/propdevguide_13484_7.pdf I need help with creating a similar layout. Basically, I need how the content is structured. It goes from top to bottom of the left hand, and then coming back to the top of the right hand, and goes all the down. If you look at it, you know what I mean, for example, pg 2 of the pdf Thanks. I am using 2007 Word See http://word.mvps.org/FAQs/Formatting/UsingColumns.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "JKwxie&q...

How to make a formula produce a result (and not the "#Value" Error)
Hello - I am trying to build a formula that shows (A1*B1) + (C1*D1). The problem is that when A1 is empty, the result produces as #Value Error, even when C1 and D1 have values. Is there a best way to address this problem? I need the results for C1 * D1 to show up, even when A1 is blank. For clarity, the formula I am using is: =AG9*Rates!$B$2+'All current'! AB9*Rates!$B$3 Thanks for any suggestions You could use this approach: =3DIF(ISERROR(AG9*Rates!$B$2),IF(ISERROR('All current'!AB9*Rates!$B $3),"",'All current'!AB9*Rates!$B$3),AG9*Rates!$B$2+'All c...

Copying and pasting mulitple formulas, can it be done?
Hello Can you copy and paste more than one formula at a time? In this instance I’m referring to a work sheet has 3 IF statements; one for Monday through till Friday is the same, while both Saturday and Sunday have different IF statements respectively. Is there away to accomplish the copying and pasting of all three IF statements simultaneously? Kind regards Tim You can copy multiple cells at once. Is this what you are asking? "Tim" <Tim@discussions.microsoft.com> wrote in message news:53595555-F6D7-4FE0-AB7D-05EA9B0FED12@microsoft.com... > Hello > > Can yo...

Help with setting up rules in Outlook
I am tryin to set up a rule in Outlook 2002 that will pop-up a notification on flagged items in my Sent Items. I want to set up Flagged items in my sent items, with follow up dates and have them pop-up when due. The rules does not let me do this. Only to follow-ups in my Inbox. Anyone know anything I can do. ...

Conditional formatting help #6
Hello, I'm starting to bang my head against my desk with this one! I have 2 columns of data: column A has 3500 rows; column C has 1800 rows. In column B I did a vlookup so I know which cells in column A are in column C. The cells that aren't in column C display #N/A in column B. I then copied column B and pasted special as value so there are no formulas in column B. This part is working as expected. My problem is: now I want to conditional format column B so that all occurrences of #N/A will be red shaded. For the life of me I can't get this to work. Can someone please t...

how can I restore Edit button to the Formula bar?
I can't put back the Edit button on the Formula bar, the Function button has taken over. JJ Do you mean the "=" sign on the formula bar? It left Excel when version 2002(XP) was released and no workaround for it. If you are running an older version, I don't know how it got lost. I can run Version 97 and cannot lose the "Edit Formula" button(= sign). Gord Dibben Excel MVP On Fri, 25 Mar 2005 11:25:03 -0800, "JJKatty" <JJKatty@discussions.microsoft.com> wrote: >I can't put back the Edit button on the Formula bar, the Function button ha...

Formula #2
I'm trying to figure out a formula that will take anything in colume F if it has Deposit or Bonus in the cell and add the adjacent cells from colum A together. For example F1, F5 and F30 meet the critiria of having "Deposit" or "Bonus" in the cells. I want to be able to the the dollar amount in cells A1, A5 and A30 (EX: $150, $75 and $300) and add them together to give me the total (EX: $525) Thank you; Steve Steve Assuming that your cells in F contain only Deposit or Bonus =SUM(SUMIF(F:F,{"Deposit","Bonus"},A:A)) If they could contain other t...

Pivot Tables for Beginners
Hello. Can anyone tell me where I can find some good starter information about pivot tables? Thank You, Judd Sandstrom http://www.cpearson.com/excel/pivots.htm <judd_sandstrom@bellsouth.net> wrote in message news:e3XTwWuyFHA.460@TK2MSFTNGP15.phx.gbl... > Hello. Can anyone tell me where I can find some good starter information > about pivot tables? > > > Thank You, > > > Judd Sandstrom > > Hi Judd, See Jon Peltier's Intoduction to Pivot Tables at: http://www.peltiertech.com/Excel/Pivots/pivotstart.htm See also Debra Dalgleis'...

HELP!!! combine 4 different sheets ???
I need to combine different Trial balances. Column A has the account number listed, colum B states if that account is a header or data row, column C is blank , Column D has the account name, column E has a reference if it is "B" Balance sheet Item or "P" if profit and loss item and column F and G have the relevant numeric data (Debit for Column F and Credit for Column G) What I want to do is incorporate ALL the sheets into one under the different header rows. (most not ALL header rows are the same in the trial balances) Can this be done -- Thanks, TKA ...

Method Add of object Validation Failed
I am getting error on the add method on validation - Method Add of object Validation Failed - please help Me.Unprotect With Sheet1.Cells(1, 1).Validation .Delete .Add xlValidateList, xlValidAlertWarning, , Lst <<<< Error here!!!!! .ErrorMessage = "Please select an Employee from the list" .ErrorTitle = "Select Employee" End With On Jul 10, 2:17=A0pm, Shokie <shokiegu...@gmail.com> wrote: > I am getting error on the add method on validation - > > Method Add of object Validation Failed - please help > &g...

VLookUp need helps
Hello all In a workbook, I have many worksheets. Each worksheet is a detail of a person with ID and grade. These worksheets are called "Detail worksheet". (for example, in a worksheet, cell A1 has 232 for ID and cell A3 has a grade of 9. In another worksheet, cell A1 has ID is 321 and cell A3 has a grade of 4) Also in that workbook, I have a separated worksheet with a list of people with their IDs and empty grades. This worksheet is called "Sumary worksheet" Now I want to input person's grade from each "Detail worksheet" for "Sumary worksheet". Do...

HELP: Need macro for excel to get data from server database and use results to populate an Access Database
Hi all, Is there a quick way to do a query on an SQL Server database and have the output put direct into an Access Database? I know how to do it by grabbing the records and then inserting each result one by one, but this takes forever to complete and I am after a quick solution, if it exists. Is anybody able to assist ? is it possible? Thanks in advance for any help! Regards Clint you could link to the SQL Server table(s) from Access, write a SELECT query to pull the records you want, and then turn the query into either an Append query to add the data to a native table in the Acces...

Formula HELP!! Please!
Hi, I have a % (changes each month) ... ex: 104% which is weighted in the following manner: The first 60% is calc. with a multiplier of 0.60 The next 40% is calc. with a multiplier of 1.45 The next 4% is calc. with a multiplier of 30 Since the overall % changes each month, what is the best formula to write so that I can drop in the % in one cell and the formula will calc. the multiplier in another cell. (So if I have 100% next month, I can leave the formula and it will know not to calculate the 4%). THANK YOU! Try this - assuming your %age is in A1 =0.6*A1 + 0.85*Max(0,A1-0.6)+ 2...

conditional format formula? help please
Folks, I would like to format an entire row in my sheet based on the text in one cell of each row. For example, when cell S33 equals "Complete", I'd like row 33 (or even just cells S1 to Snn) to assume a gray background. Now I'm no stranger to Excel and routinely build mildly complex formulas and arrays, but I can't seem to make this work in the conditional format dialog box using the "Formula is" choice. What am I missing? Anyone have a quick formula I can put in the dialog? thanks, -gb Geoff, Select Row 33 Format -> Conditional Formatting... Formu...

Help
Please take a look at the following LETTER - POSITION - TOTAL A - 6 - 34 A - 2 - 23 A - 2 - 13 B - 5 - 23 C - 2 - 55 D - 7 - 23 The above data is made up but ... I usee the AutoSort and only display LETTER A (3 rows of data) On a new worksheet how would I list something similar to th following: =============== Report for LETTER A Position - Grand Total 2 - x 6 - x =============== (with X being the value calculated) What I also see as being tricky is for it to only to include position that actually contain values. For example, there is nothing for LETTE A in 1st position so there isn...

Help with Summing Up Totals
I am trying to sum up totals in order to get an accurate picture of th style box (large cap growth, large cap core, large cap value, etc. each stock corresponds to. Right now, I have the percentage investmen for each stock of the total portfolio and then what style box it fall in (large cap growth, large cap core, etc.) How can I write a formul that will look up what style box it is then take the percentage fo that stock and then sum it all up? I know I could use a VLookup bu how do I sum them in the event that there's more than one stock tha falls into a certain style box? For exampl...

Formula to copy multiple cells onto another sheet
I have a worksheet containing names and address that are repeated onto other sheets based on criteria in another column. What formula if any can I use to determine if that person meets that certain criteria to automatically copy the 3 columns that the name, phone, and address are located in to another sheet? A little more info: I have multiple worksheets and one master list containing 6 columns: First, Last, Birthday,Phone, and Address, and class. In the class column is a variety of class names. I would like to write a macro (I have never done this before!) ,if possible, that will ...

Data Export Help Needed
What is transferspreadsheet My knowledge is extreemly limited on VB. "Douglas J. Steele" wrote: > Have you tried using TransferSpreadsheet instead of OutputTo? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "Aamer" <Aamer@discussions.microsoft.com> wrote in message > news:C9B569B2-2A0E-4075-A11F-CAA7D6AD594B@microsoft.com... > > Douglass > > > > I entered the code as you described: > > > > Private Sub Export_Data_Click() > > O...