Adding Non-sequential Cells While Excluding Zero?
Dec 19, 2012
I need to calculate ratings 1-5 of questions on client surveys received.
I have recurring sets of data, A1:E1, a number 1-5 is to be entered into one of the cells within this range. This same pattern is repeated 25 times....G1:L1, etc. At the end I need to total all the 1s, 2s, 3s, etc. Then I need to calculate an overall pct for each question based on the totals for that question, divided by the maximum score (5) multiplied by the total surveys received. The problem I am having is that my formula below is counting empty cells, and not giving me a proper pct.
=SUMIF(A1:E1,">0")/(5*$B$1)
View 7 Replies
ADVERTISEMENT
Nov 2, 2009
I'm trying to write a formula where I can add the values found in non-sequential cells in a row based on if the value in the previous cell in the same row meets certain criteria.
View 14 Replies
View Related
Jan 26, 2007
You will probably find this very easy but I am having all sorts of trouble making it work as I want to! Basically I need to do the following procedure...
1) Open an Inputbox to collect an eight digit number
2) Insert a column in A:A
3) In A1, enter a col header (URN)
4) In A2, enter the number that was collected in the inputbox
5) Enter sequential number from A2+1 to the last row
Ideally, this would be randomised, so after stage 2 do RAND(), sort, and clear contents, but if I can get the main part right I'm sure I can work that out!
View 3 Replies
View Related
Jun 22, 2009
Is there an easy way to change
='Week 1'!$D$21
='Week 1'!$D$30
='Week 1'!$D$39
='Week 2'!$D$21
='Week 2'!$D$30
='Week 2'!$D$39
to
=IF(ISERROR('Week 1'!$D$21), "", ('Week 1'!$D$21))
=IF(ISERROR('Week 1'!$D$30), "", ('Week 1'!$D$30))
=IF(ISERROR('Week 1'!$D$39), "", ('Week 1'!$D$39))
=IF(ISERROR('Week 2'!$D$21), "", ('Week 2'!$D$21))
=IF(ISERROR('Week 2'!$D$30), "", ('Week 2'!$D$30))
=IF(ISERROR('Week 2'!$D$39), "", ('Week 2'!$D$39))
Without having to change each one by hand?
There is a pattern to the numbers.
There are 5 weeks, rows 12, 21, 30, 39, 48 in columns D,I,N,S,X
View 4 Replies
View Related
Feb 12, 2010
I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this.
I have a total amount of time that a machine should take to finish a task. I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task excluding weekends, and holidays (End Date). This would be based on a certain number of "working hours" (hours in the workday minus break periods) that would be calculated in another cell.
View 9 Replies
View Related
Jan 29, 2014
I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.
I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.
Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.
I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.
View 3 Replies
View Related
Dec 31, 2009
I have four cells that I want to sum: =SUM(D3,H3,L3,P3)
I want to EXCLUDE the cell from the sum if the preceeding cell (C3,I3,K3,O3) has a value of "0".
View 9 Replies
View Related
Jun 20, 2014
Drag & Drop Example.xlsx
In the example attachment on the weekly tab, you will see I have daily totals and a sum for weekly. On the summary tab I have the columns going down with a link to the weekly totals.
I have over 5 years of data in this format and don't want to link each cell in one sheet to another. If I drag and drop, obviously the weekly doesn't come down correctly.
Is there another way as I don't want to have to do this for over 250 weekly totals (and counting) manually.
View 3 Replies
View Related
Feb 21, 2014
I have a column that says either TRUE or FALSE this is dependent on what number i input into another cell, so if i input 8, TRUE shows on every 8th row,
I want the cells that say TRUE to also have a sequential number next to it in the column beside it.
Formula i have: =MOD(ROW(S2)-1,nth_No.)=0
So for example, every 3rd row is TRUE, i am wanting a sequential number beside it:
FALSE
FALSE
FALSE
TRUE 1
FALSE
FALSE
FALSE
TRUE 2
FALSE
FALSE
FALSE
TRUE 3
View 6 Replies
View Related
Jul 17, 2014
What I want to do is Average out a range of cells "F4:F343". That's the easy part =average(F4:F343). However I want to exclude any numbers for that average if data in coloumn "Q" = "FMC"
Would that look anything like:
=average(F4:F343)/countif(Q4:Q343, "<>FMC")
I'm also displaying it as minutes in a day so i added *24*60 to the end of the formula and formatted to Number/.00. I've entered the formula and it will calculate but when i enter FMC to the appropriate area the average wont change.
View 2 Replies
View Related
Feb 28, 2008
In my worksheet i have several rows that are duplicates and i need to give each row a unique number. For example the first duplicated row needs to be 1 the second 2 etc.
View 7 Replies
View Related
Feb 3, 2009
I have three columns that i've called 'active period' and 'Date/time' and 'value'
The the value of the cells in the 'active period' column depend on the value and time.
I want to create a cell that; when i can enter a date, this date will be excluded from the 'active period' column
View 9 Replies
View Related
Apr 2, 2009
I can find the min value excluding zero in a range of contiguous cells, but how can I do it if the cells are A1, A3, A25, A67, etc?
View 9 Replies
View Related
Jan 5, 2007
I want to sum a range of cells that are referencing another spreadsheet using a vlookup function, eg:
#N/A
#N/A
#N/A
51
78
73
i dont want to delete the formulas that are returning the #N/A but i want to be able to sum only the numeric values from the six cells.
View 9 Replies
View Related
Jan 26, 2014
I am trying to calculate an average of 800 cells, where I would like to exclude a cell when a certain character is in it, namely "<".
View 4 Replies
View Related
May 13, 2009
I wanted to average selected cells excluding the value zero. Also, I wanted to make the value of cell is zero and not to display the error #DIV/0! if the denominator is zero.
A1 = Denominator
Selected cell:
A3, A5, A6, A8
View 14 Replies
View Related
Mar 25, 2009
I have tried a number of different ones from my search of this forum but I'm getting an error. here is a sample of my spreadsheet.
I want it to give me the lowest number, I don't want it to look at the 0's or if the cell is blank.
Here is my formula and my error:
=SMALL(N196:N203,COUNTIF($N$196:$N$203,0)+1)
my error is #num!
I've also tried others including using the MIN function but either 0 is entered or the error above.
3402414
View 9 Replies
View Related
Mar 28, 2008
I have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.
View 14 Replies
View Related
Nov 15, 2012
conditional formatting. I am using the code below to conditional format every Odd row with a background/border.
=MOD(ROW(),2)=1
I would like to know how to modify this, so that it only applies the format if the cell in column A on that specific row contain data and doesn't format blank cells.
View 2 Replies
View Related
Mar 15, 2013
I'd like to calculate the standard deviation and mean of a whole load of data. Unfortunately it has some errors, FALSE values, blank cells and zeroes I'd like to exclude. Calculating the average is no problem as the AVERAGEIF function works fine, but there is no equivalent for STDEV. The (array) formula
STDEV(IFERROR(E29:E32,FALSE))
ignores the cells with errors and calculates the SD, and the (array) formula
STDEV(IF(E29:E32>0,E29:E32,FALSE))
ignores the cells with a value of 0 in them and calculates SD.
How can I combine the two into one formula?
View 3 Replies
View Related
Sep 4, 2013
I have two lists of data as below, and I want to find a match after excluding "Ltd" or "Limited"
example
If Cell A1 is "ABC Limited", and Cell B1 is "ABC Ltd"
This should show as a "match", otherwise it should show error.
View 1 Replies
View Related
Jun 4, 2009
I am currently working on a football spreadsheet ranking players based on their fitness performance scores. For example, there are 14 persons on Offensive Line. I need all their test scores ranked out of 14. However, if there is a blank, I still need the test scores to be ranked out of 14.
As of right now, I have cheated, and inputted a random number to bypass the blank:
=IF($G12>10, 0,RANK($G12, $G$12:$G$25,0))
But, this now affects my average of the fitness testing scores for that position. This could be avoided if I could find a way around my first problem.
View 9 Replies
View Related
Jan 28, 2007
I am applying the sum function to a range of cells in a column. I've Auto Filtered my sheet to only include the data I need. Unfortunately, the sum function is adding everything in that column, even data from the 'hidden' cells that I've filtered out. I need to know what condition or parameter to apply to the sum function (or any other function for that matter), that will only add the data shown on my screen. Not the data that is hidden.
View 2 Replies
View Related
Jan 6, 2010
Is there a function that will count a column of numbers where that will eliminate counting empty cells based on a certain date that will also capture any numbers that is added after refreshing the table from Access? I have attached a spreadsheet for an example. I need to count on the number in column V that equals 13 for the date of 12/22/09.
View 5 Replies
View Related
Jan 16, 2012
First I will post the data:
Weekly Roster
F98AKHTAR99DEEPAK100NAPA'A101OUKASH102ROBINSON103RUSSELL104SALUNI105S
PARTIATIS106SPICE107TAN108TORRES. Jr109VAN STEEN110ACCIARITO111112113114115116117118119120
In this data, as you can see there are a lot of cells/rows that are empty. I have a macro that will copy the cells F98:F120 to F99:F121 and then copy F121 to F98, and finally delete the value from F121. In this case, it will give me a blank cell at the top and the data hasn't rotated really. All the names will be in same position.
But I DON'T want that. I want the macro to look for the last cell/row with a valid value in it and rotate the cells so that I end up having ACCIARITO at the top and everybody else to move down one row.
View 6 Replies
View Related
Jun 29, 2012
I have the following formula which works perfectly unless one of the cells has a value of zero;
=MIN(SUM(AD13),MIN(SUM(AK13)))
When AD13 = £60.00 and AK13 = £94.00 (or vice versa) the formula returns £60.00, which is correct. BUT when AK13 = £0.00 obviously the formula returns £0.00, but I would like the formula to still return £60.00.
How this can be done?
View 3 Replies
View Related
Jun 2, 2014
I have a worksheet where I capture the date and time of each new entry in column A.
In the next column, it's counting up the number of hours since that entry was made. However, it's including Saturdays and Sundays in this count and I was wondering if there's a formula to calculate the number of hours excluding Saturdays and Sundays? I know that network days would count the number of working days from one date to the next, but really need hours. Is this possible?
View 3 Replies
View Related
Dec 24, 2008
I have the following code from a form. It works just fine copying named ranges from one sheet to another. The probloem now is that I don't want to copy the characteristic of the formating or whether they are protected or not. The originating spreadsheets have cells that are protected. I am trying to copy those values over to the new sheet and paste their values or formulas but not their formatting (background color) or the fact that some of the cells are 'locked'. I want the new sheet to have the values and formulas but not the be lock for the user. Is this possible with the current way I do the code or do I have to rewrite it? How would you approach this?
Private Sub CommandButton1_Click()
Import_Data_Form.Hide
Run "NPA"
Set b = Selection
ad = b.Address
' Local Variables
Dim wkbDataFile As Workbook
' Let user select source datafile
Call UserSelectFile_WOpen(wkbDataFile)
If wkbDataFile Is Nothing Then
MsgBox "User did not select a workbook to open"
Exit Sub
End If
Application. ScreenUpdating = False
Set wb = ThisWorkbook
Application.DisplayAlerts = False
View 4 Replies
View Related
Apr 11, 2005
I need to protect my worksheet and only allow users to edit certain ranges. I am allowing the use of autofilter which I can select when I apply protection. However, I am now aware I cannot sort protected cells..which is essential. I basically need to be able to sort a column titled 'surname'. The worksheet is a record of pupils attainment in my class.
Now, I have a macro which seems to do the job: ....
View 9 Replies
View Related
Oct 1, 2009
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
View 5 Replies
View Related
