Sum Function Not Calculating
Sep 24, 2007
im currently working on a spreadsheet that tallies financial information for a few national conferences we are hosting at work. It is structured along the lines of below:......
The total is calculate using the SUM function. For sum reason the SUM function has stopped working in one of our columns. Example, if you take the above example, the SUM function for the income is working, but the SUM function for Expenditure comes up with $ - What does this mean? There are no weird values in any of the cells, the formula is exactly the same as the income column, i just don't know where it has gone wrong?
View 3 Replies
ADVERTISEMENT
Mar 14, 2014
How is MOD function used for calculating duration between two timings.
View 3 Replies
View Related
Oct 22, 2008
This is a two part question. I thank any help that can be given in regards to my problem. I have attached a spreadsheet similiar to what is used at work. We gather this information from a report we use.
Part one- in Column F, we have it set up to know how long the customer is deliquent. Column F is the difference between the date in column D and the date that is in cell E2. We are using the formula networkdays which will not count the saturday and sundays of the weeks.
The problem is, that sometimes when we place the information from the report, the value in column F is off a day, which causes us to have to adjust the formula so the information is the same. Why do we have to adjust the formula?
Part two - In the networkday formula, we do not want to include certain holidays, which is listed in column J. Is there a way to have the dates for the holidays auto advance if the dates in column J is less than the dates in column D?
View 14 Replies
View Related
Apr 16, 2014
Excel function or macros for calculating the average of waterSD column where the TT column is less than or equal to 100(red markings). functions like IF TT IS LESS THAN OR EQUAL TO 100, THEN CALCULATE AVERAGE OF WATERSD....
I had done manually on the right side in the attached excel sheet. only those yellow markings.
Because already I have some macro which do this process but it is not accounting for the TT column less than 100. it starts from 200 TT values. but i need to include 100 TT also. That's is where now we have yellow markings. I will provide you the macro code if you can edit that where it will start calculating form 100TT value, it will be really great. The code is,
[Code] .....
Attached File : average for watersd OF 100TT.xlsx‎
View 3 Replies
View Related
Feb 27, 2014
I'm having problems with a custom VBA Function, regarding with the automatic calculating values.
Basically i have a function that receives an input parameter, cell address, and returns a string, changing the cell value.
View 4 Replies
View Related
Aug 11, 2012
I am using excel 2007. I am trying to calculate the internal rate of return, without creating an array. I figured out that I can use the rate function to give me the same answer as the IRR function if I have one payment stream of say 36 months @ 1000. Where I am an running into trouble is if I have second payment stream of 6 months extension of 1500. Rate # pay 36 amt 75 PV = 5000 FV 500 , How to handle the the last 6 months ?
This is the situation
asset costs is 5000
monthly lease is 75 for 36 months
extension for 6 months is 125
end of lease buyout is 500
what is the internal rate of return without creating an array.
View 4 Replies
View Related
Jun 22, 2012
Is there a simple function for calculating the number of months between 2 dates.
Eg 1/5/2012 - 8/9/2020
View 1 Replies
View Related
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Mar 20, 2009
Another interesting dilemma to solve. Using this formula:
View 2 Replies
View Related
May 14, 2013
Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
View 9 Replies
View Related
Apr 13, 2007
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies
View Related
Nov 4, 2009
im running a new bar in Leeds and im trying to create a spreadsheet which will tell me how much to charge per drink to gain a 80% GP on products, ive got as far as listing the products and prices and calculating how much every measure (25ml) will cost. In the past ive used formulas where I can input the price i charge and it will calculate the GP (=SUM(D10/E10)*100-100) for example but id like to be able to type in the GP I want and for it to tell me the price i need, im not sure if tis possible but it will be a massive time saver.
View 9 Replies
View Related
Mar 17, 2009
I have some activities that needs to be done ina specified time
Like for example
TAT HRShrsACT 14ACT 28ACT 324ACT 42ACT 54
And i have a sheet where in i need to calculate turn around time
ActivityStart timeEnd timeTATACT 1ACT 2ACT 3ACT 4ACT 5
Based on time entered in start time and end time the TAT column should calculate the TAT based on the above standards.
I also would liuke to exclude weekends(Saturday, sunday). Also the TAT should only be calculated during working hrs between 7:30 PM to 4:30 AM.
View 9 Replies
View Related
Jul 23, 2013
I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
View 2 Replies
View Related
Dec 3, 2013
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
View 2 Replies
View Related
Jul 17, 2008
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
View 13 Replies
View Related
Nov 25, 2008
I keep getting the "You've entered too many arguments for this function" error.
Here is the formula:
=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))
View 6 Replies
View Related
May 13, 2013
I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
View 1 Replies
View Related
Mar 2, 2008
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).
My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.
My Script:
Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult
Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select
If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If
End Function
So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).
My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
View 9 Replies
View Related
Oct 25, 2007
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network.
Edit:
Charges to network A is 1,79,- per minute after the first 20 minutes are spent.
Charges to network B is 2,29,- per minute after the first 20 minutes are spent.
To sum up:
1. The customer makes a call.
2. If there there are available free minutes, these should be spent first.
3. The customer is charged per minute, depending on network called.
View 14 Replies
View Related
Mar 1, 2008
I am on an auto show board and want to come up with a way of calculating the ballots that come in, using excel. Ballot points are accrued as follows: 5 points for 1st place, 3 points for 2nd place, 1 point for 3rd place. With about 25 different categories (classes) and I am estimating participants at around 100.where I can in the automotive field knowing there is nothing besides personal gain.
View 12 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
Dec 5, 2008
For my Intro to Engineering class, I collected data of two filter media in the laboratory. The data included the % of the media which passed through a sieve and the sieve openings. For use in a certain equation, I need the opening size at the 10th, 30th, 50th, 70th and 90th percentiles. I've tried searching on the Microsoft website but I don't think the PERCENTILE function will work correctly with the given data. I also tried using the slope between points to find the opening sizes at the percentiles mentioned, but doing so yielded different results from my professor's. Attached is the data I collected organized in tables and graphs of the data. I have also attached a pdf file of an example which my professor has given us.
View 5 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
Apr 17, 2009
Problem - billing spreadsheet for prisoner fee.
1 - 8 hrs = $55
9 - 24 hrs = $55 + $65 or $120
Anything over 24 hrs - $65 for each additional (24 hrs) ($185)
So if you were locked up for 6 hrs it is $55. If you were locked up for 18 hrs it is $120. If you were locked up for 28 hrs it is $185. And if you were locked up for 49 hrs it is $250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.
View 5 Replies
View Related
Jun 9, 2009
In order to compute and forecast an estimate of the triennial rent adjustment, i need to compute the rise in the cpi (table) from the lease date (cell c3) and the present date (cell a2) or the closest publishe date existing in the table. The % rise is easy to calculate present index / opening index -1 = % rise in cpi. I update the index monthly when onthe bls publish it.
View 3 Replies
View Related
Jun 20, 2009
I input a month as a start date, and then want the next x number of cells to increment by one month based on the start date. I thought this would be easy using the preset DATE function but I can't get the thing to do what I want.
View 9 Replies
View Related
Nov 12, 2009
Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer.
View 14 Replies
View Related
Nov 24, 2009
I had an excellent response last time I posted here, this time I’m stuck again with a new formula. I’m trying to calculated amounts between different times, but keep tying my self in knots with complicated IF formulas.
Is there an easier way to work out hours worked between 2 times, but too complicated things further I need three separate amounts so I’m guessing I’ll need three separate formulas
Hours between 00:00 – 06:00
Hours between 06:00 – 19:00
& hours between 19:00 – 00:00
An example could be, 05:00 – 20:00 should be 1,13,1
View 11 Replies
View Related
Jun 20, 2007
I have run into a problem I have not found a solution for surprisingly. I need to calculate several fields on the form prior to saving it and thus exporting the data to the worksheet. All I can find is how to calculate the field in a worksheet and then reference it with VB code on the form. Is what I want to do possible?
If I run into many more problems I may want to shop this project out as my schedule barely allows for the work I have let alone attempting to learn VBA on the fly.
If anyone is interested I can post the project so far along with the detail of what I want it do in the end.
View 9 Replies
View Related
