Calculating Ratios With Zeros In Some Cells?
Jun 30, 2014
I am calculating ratios to show as 1:0 or 3:2 etc using this formula: =IFERROR(CONCATENATE((K2/GCD(K2,N2)),":",((N2/GCD(K2,N2)))),"Nil")... I have a lot of columns that have zeros in them and the formula i am using doesn't seem to work for those...
Example
A B
3 6 should equal: 1:2 works OK
2 0 should equal: 2:0 my current result: 1:0
3 0 should equal: 3:0 my current result: 1:0
0 1 should equal: 0:1 works OK
0 0 should equal: Nil works OK
4 1 should equal: 4:1 works OK
Anything with a zero in the first column comes up as 1:0
View 5 Replies
ADVERTISEMENT
May 10, 2007
I have a one column of numbers...let's say consisting of six rows
the following numbers 12,0,14,25,0,9
if I average this it comes out to 10
how can I average the same rows but exclude the zeros in the outcome(is there a formula to do that?)
I would like the answer to be 15.....I still need the zero's in the rows however
View 9 Replies
View Related
Jan 9, 2014
I am fairly new to spreadsheet and need to develop a simple spreadsheet that will present a actual vs budget % for certain clients on a weekly basis.
It is currently taking me forever to remap my formula each week when i have populated my actual vs budget data.
On Sheet 1 it has my data and on Sheet 2 is where my ratios are presented with graphs.
On Sheet 2, the ratio is presented in one cell with a formula that calculates based on two cells adjacent to each other on Sheet 1.
I am currently running my report horizontally across the spreadsheet.
The problem is that when i add in another week running horizontally across my spread, the formula can not be dragged across as it does not jump by two cells.
I have attached a example : example.xlsx‎
View 6 Replies
View Related
Jan 30, 2014
What I need is a formula for the following:
If Column A equals "No Show" and Column B equals "Anthem" then sum of cell C2 divided by D2
Every time I input this simple little formula I get an error.
A
B
C
D
Status
Coverage
Counts
No Shows
No Show
Anthem
5
10
View 1 Replies
View Related
Feb 13, 2009
I am trying to work out how to convert the slope/gradient from degrees to %, and ratio.
View 7 Replies
View Related
Jul 17, 2006
The attached worksheet is for giving competitors a score of 1 if they meet certain criteria and 0 if they don’t. Need to write a formula that warns me if the three highlighted ratios are not meet. The last Ratio (15) is on a sliding scale so must be at least 1.
View 6 Replies
View Related
May 22, 2006
This is what I’ve been trying to do. There are 3 different work schedules for each employee. Each employee gets 2 days off in a row. At the top of the employee schedule worksheet, there are 3 schedules. The zeroes represents days off for that schedule and the 1’s represents days worked. Ok, Cells c7 thru c17 display total staff needed for that day Cells c8 thru c18 display average number of customer on a particular day Cells c9- 19 display number of needed based on a staff to customer ratio of 1 employee per 80 customers B11 displays that ratio
I need to make a comparison of two different staff to customer ratios. My goal is to find lowest total payroll while adequately staffing the store. In B13 I want to enter the daily salary amount for 1 employee at $10 hr/8 hrs per day
In A16 enter total payroll amount
Total staff B7 times salary in B13
Use solver to find minimum payroll amount
Target cell is total payroll found in A16
Target cell must be = to minimum value................
View 3 Replies
View Related
Oct 24, 2013
I have a spreadsheet for which I have to set up a formula to get the minimum value from a range of cells, but that range can include blank cells, errors (#DIV/0) and zeros, all of which I want to be ignored. I can work out how to ignore EITHER the zeros
(=MIN(IF(C10:G100,C10:G10)),
or the error cells
(=MIN(IF(ISNUMBER(C9:G9),C9:G9)),
How to exclude both. If I try to combine both of these exclusion criteria it doesn't work and I end up with the answer #DIV/0, which is one of the values I want it to ignore.
View 8 Replies
View Related
Oct 12, 2009
with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.
What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.
Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.
View 4 Replies
View Related
Nov 7, 2009
I’ve created a formula for this statistic and I’m happy with the results. Because I’m working with formulas, my only problem is the unwanted zeros. How do I hide zeros that show up automatically (i.e. #3 [blank] and Nov 09-June 10)? I can hide the numbers, but if I enter a zero to one of my future statistics it will not appear and I don’t want that to happen. Is there a way to hide those automatic zeros without affecting my real zeros?
Vendor’s Name
Jul 09
Aug 09
Sep 09
Oct 09
Nov 09
Dec 09
Jan 10
Feb 10
Mar 10
Apr 10
May 10
June 10
1
Vendor1
20
5
15
3
0
0
0
0
View 9 Replies
View Related
Aug 8, 2007
I want to take an average of only non-zero numbers in a range of cells containing a few zeros. I know there is a function to do this, but I haven't been able to find it. With the normal average() function, the denominator is too large as cells with 0 are included.
View 9 Replies
View Related
Nov 28, 2007
I am basically making a sheet that counts attendance, everytime an X is placed in a particular cell another cell counts that x. So if there are 25 x’s in one row, another cell will automatically add those x’s to show 25. That isn’t my problem. My Problem is the cell that contains the countif formula that adds the x’s are all 0 if there is no data for it to add. I want to make it blank because right now I have a whole bunch of 0’s going down one column and can’t figure how to get rid of it.
Right now I am using: =COUNTIF(C20:X20,"x"). I normally would take the time to research and look through your forums however I do not have the time at the moment and was hoping for a fast answer.
View 4 Replies
View Related
Sep 19, 2009
i have a mock up football issue here i want to enter scores in sheet 1 and they automatically fill sheet 2 etc i know i can just =and copy sheet 1 A5 and so so but that leaves zeros which will start allmy formulas with 1 point i need a formula that will leave sheet 2 3 etc blank untill any score is entered
View 3 Replies
View Related
Oct 21, 2009
i am creating a football prediction sheet, and have a problem where blank cells are treated as zeros.
The enclosed spreadsheet shows the formulas in green working fine, but the blanks are treated as zeros in cells j6, I7 & J7.
View 11 Replies
View Related
Sep 15, 2014
I had been trying to calculate cells that contain numbers with leading zeros.
I can't convert the cells to TEXT because Formulas don't work in TEXT Cells.
I cant use Custom Formatting because the rows may contain different number of leading zeros.
Also Is there a way that I can trim the numbers from Col A and put them in C?
Currently I don't like having to change the RIGHT(G2, 3) to RIGHT(G2, 4)
if the number is smaller than the number of char. option.
View 9 Replies
View Related
Jul 15, 2014
excel formula to keep leading 0's in a cell. ex: "0069" to 0069, "000123" to 000123 and so on.. Attached file for the same.
View 3 Replies
View Related
Oct 31, 2012
Here are my cells and values
B38 C38 D38 E38 F38 G38 H38 i38 J38 k38 L38 M38 N38 O38
0.00
None
1.95
16
1.73
21
0.00
None
20.10
20
1.73
20
0.00
None
What I would like to do is average only every other cell AND also not count zeros. So =average(B38,D38,F38,J38,L38) generally the idea not averaging H38 & N38 since they are both zeros.
View 3 Replies
View Related
Dec 16, 2008
i need to connect some cells, but have to check to make sure cells dont have zeros in them first
example m13 m14 m15 m16
................5......10......0........15
in cell q18 i want it to show this 5/10/15, dropping the zero cell
View 9 Replies
View Related
Oct 10, 2007
I would like to average a non-continuous range of cells while also excluding all zeros.
I am averaging hours worked and the hours are found in cells:
B2, D2, F2, H2, J2, L2, N2
Some of these cells contain zeros at this time and I do not what to include the zeros in the average.
I have found formulas that would work but they are all with continuous cell ranges.
View 5 Replies
View Related
Feb 21, 2008
Here is a question about the chart in excel. I have a sort of data where only part of the data is in numbers, and when drawing the chart, the whole range of data were selected. Then on the chart, there is a line drop to the x- array, anyone knows how to get rid of it? It is a line chart. Here's an example if i did not clearly describe the problem
Here's data
X Y
4354.28
5354.75
6352
7343.82
8aa
9aa
10aa
11aa
12aa
and between 7 and 8 on x, the line will drop from 343.82 to 0, and i don't want this part, i want the chart to show to 7 and leave the 8 to 12 blank..
View 2 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
Sep 6, 2006
I have 2 columns which contain numbers that have laeding zeros. example:
Column H has 002345 and Column I has 0678. I have tried H1&I1 but the leading zeros are stripped off. Is there a way to combine 2 cells AND preserve the leading zeros in both cells ?
View 3 Replies
View Related
Oct 25, 2012
I have managed to retain leading zeros in a cell by formatting the cell and selecting Number and Custom and adding the maximum number of zeros I want to have in front of the number in the cell i.e. 000002.
I want to have another cell in the spread sheet which contains the value 35394000002 using the formula =A24&B24.
When I use this formula I get 353942. How can I retain these zeros?
View 1 Replies
View Related
Jan 28, 2013
I have 4 columns (and a couple of other text columns) that are formatted as decimal with 6 dicimal points.
I need to create a text file with lines that are 80 bytes each from the spreadsheet - no spaces inbetween each of the characters.
The issue I am having is trying to format the 4 decimal point columns to be fixed numbers and retain the leading or trailing zeros to do a concatenation with the other columns.
Just an example of some what some of the values look like on my spreadsheet:
.123456 | .123450 | .012345 | .012340
Essentially when I'm all said and done, I would like the row to look like "text123456123450012345012340text"
I tried to format 4 new columns as general and use a "=right(cell,6)" and that worked pretty well for the cells that have leading zeros, but for the cells that have trailing zeros, it doesn't seem to pick up the zero. I tried to use a "=mid(cell,2,6)" but that didn't work either.
View 5 Replies
View Related
Nov 14, 2012
Is it possible to keep ending zeros in cells that are formatted for Text or General?
I have a column with numbers like the following: 264400
I need to format this number for three decimals so it will look like the following: 264.400
I need the column to be formatted for either Text or General. Currency breaks a system.
View 1 Replies
View Related
May 29, 2013
I am trying to do the following.
Monday=0
Tuesday=5
Wednesday=10
Thursday=0
Friday=5
Saturday=0
Sunday=0
Averages
Monday=0
Tuesday=5
Wednesday=7.5
Thursday=0 (I have got a value return of 7.5)
Friday=5
Saturday=0 (I have got a value return of 5)
Sunday=0 (I have got a value return of 5)
I need to work out averages for cells higher than zeros, in other words, I need to ignore those.
Also say I have got an average of 5 by Tuesday and no number yet for the rest of the week, I still get an average of 5 for all days left which I do not want.
I am using excel 2003 and formula =SUM(RANGE)/COUNTIF(RANGE,">=0").
I would also like to hide the annoying #DIV/0! error.
View 9 Replies
View Related
Aug 30, 2007
I have a summary worksheet consisting of columns that are referenced to other sheets within the same workbook.
If the cell in the 'referring' (original) sheet is blank then the forumula returns a zero instead of a blank in my summary worksheet, which is messing up some other calculations.
Is there a way to make these cells truly blank, or possibly into string variables instead, so that they do not interfere with my calculations?
View 13 Replies
View Related
Oct 17, 2008
Code:.....
I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.
View 4 Replies
View Related
Mar 6, 2008
I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:
Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.
Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.
The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.
So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.
I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.
View 9 Replies
View Related
Nov 9, 2008
I am having a problem with some of the cells in a spreadsheet not calculating.
For example, in column A, all the cells have formulas that pull data from another sheet. When I enter the formula, only the formula shows (not the result). I can't figure out what is wrong as the formulas in column B work fine (the results are showing, but not using the same data that column A is).
I am thinking it may have something to do with the data being used to calculate the formulas but not sure. How can I resolve this?
View 7 Replies
View Related
