Calculating A Geometric Mean With Some Negative Values 
Calculating A Geometric Mean With Some Negative Values  Excel 

The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.
Similar Topics
Hoping someone can help me quickly as I'm pulling my hair out and have a deadline getting too close.
Been trailing the net and this board but can't work out how to do a simple variance.
How do I work out the difference between two numbers  including negatives.
If the numbers are all positive, it's fine as it's simply a case of A  B = C which is your difference. However, that doesn't work if B is negative.
I need a formula that would give the following:
First Number / Second Number / Difference
1 / 5 / 4
1 / 5 / 6
1 / 5 / 6
1 / 5 / 4
5 / 1 / 4
Hopefully there's a simple function I've been missing.
Thanks in advance, R
I was thinking of trying a nested if/and statement but I haven't quite figured out how to do it. Basically I want to identify the two values where it switches from positive to negative and also indentify the values where it goes negative to positive, I can then fit a straight line between them to find a better approximation of the intercept (though it might not be necessary). Preferably I'd like it all one function as I'm not doing it in VBA (I might do later though, we'll see).
Can anyone suggest how I'd find these value or the xintercept. Any help would be greatly appreciated.
Instead of the y axis being on the left hand side, i wish for it to cut at 0,0. I have positive and negative values and I need it to be in the centre instead of on the left.
I have tried formatting both the axis and it would appear that the x axis already cuts the y axis in the right posistion so i need to format the x axis to make the y axis cut it at 0,0. I have already tried typing in 0 instead of 1 and it keeps saying it need to be number more than or equal to 1.
Any help would be greatly appreciated.
Nicole
lets suppose my numbers a 1,2,3,4,5,6,7,8,9,10,11
I want to generate all possibilities of the 6 numbers. But i dont want duplicate series.. I mean 1,2,3,4,5,6 and 6,5,4,3,2,1 are duplicates.
ANY HELPS ARE GREATLY APPRECIATED
I can calculate the formula for this , but the problem comes where for eg there is a loss in Jan 2008 for 95415 and there was a profit in Jan 2007 for 80215. How do I set up formula to take negatives & positives into account in calculating the increase or decrease/
Your assistance or anybody's on the forum will be greatly appreciated
Regards
Howard
http://www.mrexcel.com/forum/showthr...ghlight=howardneed
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" > 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
for example: if an employee works 10 hours a day we would not count towards overtime until the employee completes a 40 hour work week.
Any suggestions would be greatly appreciated!!!!!
Thanks,
YV
I'm a bit of a newbie with Excel, but...
I have several cells with data that contain both letters and numbers (4H, 8V, 4FH, etc.) What I want to do is remove only the text characters from these cells, and add the remaining number values together among a series of cells with this data type.
If your answer involves using a macro or VB, please provide a link on how to use the formula. I've never used a macro or VB.
Thanks.
If that number is less than 100, I want it to show as blank.
Any help?
Right now, I'm trying to accomplish this by making a conditional format...meaning when my cell equals less than 100 I make the cell color and font the same (so it looks blank, but its not). When I print it, it still shows the negative value.
Thanks.
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell  then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
include/exclude one of the series to see how the chart changes, all the
colors change and we have to spend time reidentifying which series is
which, using the legend to check the colors.
Is there a way to "lock" the colors, so that the bars stay the same colors
regardless of whether other bars are included/excluded?
Thanks!
Keith
I have created a complex formula to help me calculating Golf handicaps for players.
The formula is in the first cell and I want to drag the formula across to the right to cover the 18 holes.
But ALL the values change and I want some to change and some to stay constant.
How do I tell excel to drag the formula across but keep certain parts of the formula the same..ie ....=D11.... in the formula when dragged across doesn't go D12,D13;D14 in each cells formula and should stay as D11 for all.
Many thanks for your help
Ole
This is what I have so far (hire date is in column B):
=((TODAY()B4)/365)&" YEARS"
This function gives me a number with many decimal places.
I tried:
=ROUNDDOWN((TODAY()B4)/365),0)&" YEARS"
It tells me I have too few arguments. Please help!
bring up a menu anymore. It happens only when I'm in Excel only... it still
works even if I bring up macro editor within Excel... it only doesn't work
when I'm in Excel. I've tried Options and Customize... nothing there seems
to work...
HELP!!!
I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:
01/01/2007
02/01/2007
03/01/2007
I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.
Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!
Many thanks,
Caitlin
Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...
I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values
Thanks
I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:
Column M  Estimated Delivery dates
Column N  Actual Delivery dates
Column O  =IF(SUM(M2N2)>0,1,0)
Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))
This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?
Any help much appreciated
Thanks,
James
Im unable to put any pics up as my work pc has java disable so photobucket ect won't work but has anyone else come across this problem and how did you rectify it? A search macro perhaps?
I am brand new to Mr. Excel and would love some advice.
I searched the boards pretty extensively but could not find what I am looking for...I apologize if this is a duplicate.
I am using Excel 2007
How do you automatically add rows and update values for cells to a linked worksheet in which rows have been added? For example: Sheet 1, columns A & B are linked to Sheet 2, columns A & B. Sheet 2 has values in A1:A5 & B1:B5 and Sheet 1, since it is linked, has the same info. I want to add a row in between 3 & 4 on Sheet 2 and want Sheet 1 to automatically add the same row and update the value of the cell in column A & B.
Any help is greatly appreciated!
John