Working With Percentages Over Many Cells
May 10, 2007
I have 100's of cells with various formulas similar to this: =U7*9.57. U7 Being the cell with the number of hours worked and 9.57 being the hourly rate. Fairly simple so far. Now there has been a 5% pay rise so unless i get another formula to add 5% on to all these cells i will have to manualy change each hourly pay to the correct value, something i dont want to do. So my question is can i do something like =U7*9.57 then another formula to add 5% on to the 9.57, then i can copy the formula down all the other cells?????
View 3 Replies
ADVERTISEMENT
Aug 20, 2008
I have a column of data with students marks ranging from zero to 78(S17:S74). I need to work out the mean score of the bottom 20% of all the scores achieved - Is this possible. I'm no expert so please keep it as simple as possible! I'm working with Windows XP and Excel 2002.
View 9 Replies
View Related
Jun 27, 2014
I need a formula to create the following:
In Cell F5 is 26, so the percentage should be 101% (100+1). How can I modify the below to add 1% when the number goes over 100%?
Do I have to create two columns? one for the below and one if the F6 is over 100%?
IF(F6="N/A","N/A",IF(F6>=25,1,IF(F6>=19,0.8,IF(F6>=13,0.6,IF(F6>=7,0.4,IF(F6>=1,0.2,IF(F6
View 5 Replies
View Related
Jun 25, 2006
I am using an excel loan amortization schedule but I need to add another cell to calculate percentages. how do I do this? I need to know the command.
View 2 Replies
View Related
Jun 27, 2008
formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula
View 9 Replies
View Related
Oct 31, 2011
I am using the find function to establish the row number in a spreadsheet for an edit process.
Code:
wks.Range("B:B").find("something").Row
the cells in Range("B:B") are linked to another worksheet. If I copy paste values on the cells in Range("B:B") the code above works a treat.
View 4 Replies
View Related
Mar 26, 2014
Sheet 3 column c is where the formula is located - pulling data from b14master (sheet)
Worksheet attached
View 4 Replies
View Related
Feb 7, 2012
im working with a number of related cells and am having trouble with entering formulas. at the moment im entering them in manually but im hoping theres a better way.
suppose I have a few groups of data. the first group is A1-C1, second group is D1-F1, etc. the values of these cells have to be output somewhere else. so if A1=dog, B1 = cat, C1=car then AA1 would be dog cat car. AB1 would be the concatenation of the next group.
Now my problem is how do i put a formula into AB1, AC1 etc. I cant drag and drop. If I drag AA1 to AB1, AB1 will be the concatenation of B1-D1 instead of D1-F1.
As an aside one thing I thought of is to just put the output cells further apart. So if the first output was to goto AA1 the next one could be output to AD1. The only reason I didnt do this is this sheet is wide enough anyway I didnt want to make it any wider. I also thought of outputting D1-F1 into AD1 and then copy/pasting those values into AB1 but that wouldnt work as I need these values to be changeable.
View 1 Replies
View Related
Jul 20, 2009
I am working with a list and would like to show the top 10 careers in %.
Out of 914 votes __ % are interested in Nursing, __ % are interested in ABC, ___ % DEF, etc.
View 2 Replies
View Related
Dec 4, 2009
i have a figure of 395.00 euros to start and need to calculat percentages when i enter a certain figure ...
View 8 Replies
View Related
Dec 30, 2013
=SUM((DATA!$F$2:$F$3000=A3)*(DATA!$N$2:$N$3000="N")*DATA!$J$2:$J$3000)
This formula doesnt work but =SUM((DATA!$F$2:$F$25=A3)* DATA!$N$2:$N$25="N")*DATA!$J$2:$J$25) works. Thats because all the other cells higher than 25 have a reference to another cell. How to solve it? Maybe with SUMPRODUCT / ISBLANK? See attached file.
View 3 Replies
View Related
Oct 9, 2013
For certain cells in this spreadsheet Excel's find and replace is not working:
For example, if I type the species "carex_filamentosa" it will say "...cannot find the data you're searching for"
View 2 Replies
View Related
Oct 13, 2009
In range D2:D7 there are formulas with dates where message should return saying please check the date which is overdue but the codes does not work
In range D8:D11, these dates are normal without formulas and works with the code
DueDateFail.xls
If I change the code in Sheet1 to D2:D7 the codes won't work but do work with D8:D11 because the dates in D8:D11 don't have the formulas like D2:D7 has
View 2 Replies
View Related
Aug 8, 2012
My goal is to get all numbers that are negative in my A column to be positive.
I filter column A to show only records with negative numbers (there are now hidden cells) (simple example, A2=-1 , A3=2, A4=-3, filter so I can now only see A2 and A4). I insert a column (B) and do "=abs(A1)" and drag that down. This works to get the absolute value of column A into column B, for only the visible records. (so I still only see rows 2 and 4; B2=1, B4=3)
Now the hard part. I want to copy these numbers to column A, but only paste to visible cells. I highlight my data range in column B and hit Alt + ; to only select visible cells. Now I hit Ctrl + c to copy. If I then click on the first cell in column A that I want to paste to and hit Ctrl + V, the data is pasted in all cells, no spaces, filling in the hidden cells, which I do not want (in my example the 1 from B2 goes to A2 and the 3 from B4 goes to A3).
If I select my entire 'paste to' data range in column A (A2:A4), hit ALT + ; to select visible only, and hit CTRL + V to paste, it tells me "This command cannot be used on multiple selections".
AOther postings of seen seem to intimate that my first attempt described here should work (select visible only, copy, then paste), but doesn't! i can copy visible only easily enough, but when I paste, it ALWAYS pastes into all rows, hidden or not.
View 7 Replies
View Related
Sep 13, 2008
I've worked with Excel for many years and have just begun writing VBA in Excel 2007, so I am really stumped by this:
SR = 1
SC = 1
TR = 40
TC = 2
Worksheets("ChartData").Activate
' Works
ActiveSheet.Range("A1:B40").Select
' All of the following Fail w/1004 Error
ActiveSheet.Range(Cells(1, 1)).Select
ActiveSheet.Range(Cells(1, 1), Cells(40, 2)).Select
ActiveSheet.Range(Cells(SR, SC), Cells(TR, TC)).Select
At first I thought I had a bad install,
but this is happening on two manufacturer installed systems...
And I seem to be following the documentation for the use of Range with Cells.
Am I missing something?
View 9 Replies
View Related
Jun 10, 2009
i have the following code that works ok with single cells but wont work with merged cells
what is supposed to happen is the row is highlighted when double clicked anywhere between column 4-52 and reverts back if double clicked again
But this will not work if the cells are merged.
Also can the colours be changed to RGB colours and not standard colours
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Cells(Target.Row, 4).Interior.ColorIndex = xlNone Then
Range(Cells(Target.Row, 4), Cells(Target.Row, 52)).Interior.ColorIndex = 4
Cancel = True
Else
Range(Cells(Target.Row, 4), Cells(Target.Row, 52)).Interior.ColorIndex = xlNone
Cancel = True
End If
End Sub
View 9 Replies
View Related
Oct 14, 2006
how i can display different values using the Selection.FormulaArray, ok look at my
Dim mySheet As Worksheet
' On Error GoTo errors
Dim x As Integer
x = TextBox1.Text
Dim y As Integer
y = RefEdit2.value
Set mySheet = Excel.ActiveSheet
mySheet. Range(RefEdit1).Select
Selection.FormulaArray = "=multiplie_value(""" & x & """, """ & y & """)"
MsgBox Selection.FormulaArray
Now the function i call:
Public Function multiplie_value(valor As Integer, multiplo As Integer) As Integer
multiplie_value = Val(valor) * multiplo
End Function
thats an example. so the real question is i create a form, the user enter some values to search to a database, the user select an area(a range) where they want the data to be display, but i dont how to display that info that is coming from the database in the range that the user selected. The info displayed in the range should look like a tabular report, column headers, etc.
View 9 Replies
View Related
Mar 10, 2009
I think a really easy no brianer, I need to find the difference in percentage terms of two values. I am using =(B2-A2)/ABS(A2)
where B2 = 24.09
and A2 = 21.08
View 2 Replies
View Related
Oct 15, 2009
I am monitoring the balance in my Allocated Pension fund, via the WWW.
In particular, I am recording the profits (disregarding costs, outflows, etc.) as ANNUALISED PERCENTAGES (a new line, for each week).
My formula is:
=(accumulated profits/principal)*(365/elapsed days)
Questions:
Will my formula remain valid, when “elapsed days” exceed 365?
If not, is there a FUNCTION that can handle my problem. And
What would the function’s ARGUMENTS be?
View 3 Replies
View Related
Jul 26, 2006
I'm sure this will be an easy calculation for most of you...........
If I have two numbers, ie. 390 and 217, how can I calculate the difference
between the two figures as a percentage? (These two figures represent sales
in two months and I need to know the difference in percentage terms).
View 10 Replies
View Related
Mar 14, 2012
I would like a way to use conditional formatting to highlight percentages that are over 50%. The problem is that I have other numbers in the same column that are not percentages but I would like to ignore them for highlighting purposes? Can this be done without sorting the columns?
eg.
345- ignore
Ken- ignore
2500- ignore
45.6%- do not highlight
65- ignore
92%- highlight
View 5 Replies
View Related
Nov 5, 2013
=IF(A2-B2,(B2/A2))
The simple formula above calculates percentage which basically in this example, 159-90 divded 159% which equals 56.60%. What i need the formula to recognize is if both the entries are 159 which should show 100%. Its to show error rates by the way. Percentage wise.
View 3 Replies
View Related
Jul 2, 2007
Am trying to do a formula to work out a percentage of one figure from another, cannot for the life of me think it through properly!
So in
Cell J73 I have the net income and in cells J75 +J77 I have the total outgoings.
I want a formula that works out the percentage outgoings, so if someone is earning £100 and they have £20 outgoings, that is 20% of their earnings.
View 9 Replies
View Related
Jan 4, 2008
I am trying to combine to cells containing percentages, however when I do the percentages turn into decimals. I have tried using the text funtion, but that just returns the full number 1, no decimals or percentages.
View 9 Replies
View Related
Mar 2, 2008
I work at a library and have been asked for help by the Asst. Director. The problem is, I have next to no Excel experience, just what I've been reading in the Help files and Online.
We are using Excel 2003 and running XP. Here is his predicament: he has an excel document that lists the books we have ordered from a publisher. Column I has the MSRP for the book and Column J has the discounted price for the book. We are trying to get Column L to show the percentage of the discount that we received. This is what i've come up with:
example for line 255 typed in as formula for L: =1-7.95/14.98
It comes out correct when I type in the formula as I show above, but when I try typing in the formula (as it should look:=1-J255/I255) , i get an error message: #VALUE! ...
View 9 Replies
View Related
Jun 17, 2008
I have a table of data with names across the top and number of weeks down the left. The table lists amounts of money those names earned during the respective weeks. It looks something like this:
Mike Dan Bill Carl
400 500 600 700
800 900 1000 800
700 600 500 400
In a seperate worksheet within the same workbook, I have 3 columns of data. The first column is a name. The second column is a criteria. In the third column, I am trying to look up the value of 8 specified weeks of earnings of the name I put in column 1 and multiply those earnings by a percentage based on the criteria in column 2. Then sum the products.
I have a table of the 8 percentages I am trying to apply. There are 7 different percentage applications.
So if I am looking up Mike's earnings during those 8 specified weeks and the criteria in column 2 is "B", then I want to apply the appropriate column of percentages to those 8 looked up earnings, Multply them and then Add them.
Up until now, I have been trying to use some hybrid of HLOOKUP, SUMPRODUCT, and IF.
View 9 Replies
View Related
Sep 16, 2009
I have data that I import from another source into excel and it pulls over like this
3866.00%
435.00%
125.00%
1254.00%
I want to remove the % since these aren't actually percentages, the report we pull from has them listed incorrectly and cannot be changed since it is software driven, not excel driven. The problem is simply remove the % changes the number to 38.66 instead of leaving it at 3866.
View 9 Replies
View Related
Jun 24, 2006
I have a row of formulas that calculates information into a percentage. As you input info, a percentage is created in the cell. Those cells that do not yet have information entered still show " DIV/0" or something like that.
I need a formula that will average all the percentages, but the problem is, it's trying to also average the formulas with the percentages, so I currently get an error.
View 9 Replies
View Related
Aug 11, 2009
I made a spread sheet last year that used various COUNTIF formulas that referenced other cells. =COUNTIF(I40:ER40,"A") It worked perfect.
I copied that spreadsheet to use for this year. I cannot get this formula to work. I have reentered in various locations and forms, but to no avail. If I enter the data and then enter the formula, it works.
However, if I enter the formula first and then enter the data (which I will do over the course of the year) it does not work. I even tried to reconstruct all of this in a brand new spread sheet and it still is not working, although last year's spread sheet still works.
View 3 Replies
View Related
Aug 28, 2009
I try to edit comments in cells of a certain column and nothing happens. I am able to edit other comments throughout the spreadsheet, but when I select "edit comments" for these particular cells, no edit box is displayed for editing. It worked fine a couple of weeks ago.
View 11 Replies
View Related
