Nesting or joining two formulas???

I currently have two formulas that I'm needing to somehow join togethe
but I have not been able to do this legally.

{This formula gives a sum of any time over 8 hours and displays i
using a decimal value.}

My cells are formatted to 0### for cells C10 through F10 and cell H1
is converted to decimal using the formula below.

[this formula located in H10]
=IF((F10-E10)+(D10-C10)>800, (F10-E10)+(D10-C10)-800, 0)/100


{I still needed the above formula to display in quarter hou
increments, ie: .15 = .25, .30 = .50 etc.  This was submitted to me b
JanetW and works great seperately when placed in I10}

=TRUNC(H10)+IF(MOD(H10,1)<0.15,0,IF(MOD(H10,1)<0.3,0.25,IF(MOD(H10,1)<0.45,0.5,0.75)))

My problem is that I need to incorprate both of these into one cell bu
I don't know how to go about doing it. I've tried a hundred differen
combinations with no luck. Any help is greatly appreciated and I kno
that this is probably as clear as mud so if you need more informatio
please e-mail me at jmcbride5@cox.net .  

Thanks for all of the help. I'm very pleased with this site and th
assistance that everyone has to offer

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/19/2003 8:17:06 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
597 Views

Similar Articles

[PageSpeed] 2

Try =CONCATENATE(x,y) where x is one forula and y is the other (minu
the equal signs).

Is that what you needed

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/19/2003 8:49:26 PM
I'm given the Circular Reference message and haven't used  cirula
reference before. Would you know what I need to do

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/19/2003 9:34:19 PM
=INT(MAX((F10-E10)+(D10-C10)-800,0)/100)+ROUNDDOWN((MOD(MAX((F10-E10)+(D10-C10)-
800,0)/100,INT(MAX((F10-E10)+(D10-C10)-800,0)/100)))/0.15,0)*0.25

will match the results of your two formulas I think, but have you considered
using real times instead of lookalikes?

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
Newsgroups - Where you really can get a free lunch!!
----------------------------------------------------------------------------



"Jmcb1b" <Jmcb1b.x5qva@excelforum-nospam.com> wrote in message
news:Jmcb1b.x5qva@excelforum-nospam.com...
>
> I currently have two formulas that I'm needing to somehow join together
> but I have not been able to do this legally.
>
> {This formula gives a sum of any time over 8 hours and displays it
> using a decimal value.}
>
> My cells are formatted to 0### for cells C10 through F10 and cell H10
> is converted to decimal using the formula below.
>
> [this formula located in H10]
> =IF((F10-E10)+(D10-C10)>800, (F10-E10)+(D10-C10)-800, 0)/100
>
>
> {I still needed the above formula to display in quarter hour
> increments, ie: .15 = .25, .30 = .50 etc.  This was submitted to me by
> JanetW and works great seperately when placed in I10}
>
>
=TRUNC(H10)+IF(MOD(H10,1)<0.15,0,IF(MOD(H10,1)<0.3,0.25,IF(MOD(H10,1)<0.45,0.5,0
..75)))
>
> My problem is that I need to incorprate both of these into one cell but
> I don't know how to go about doing it. I've tried a hundred different
> combinations with no luck. Any help is greatly appreciated and I know
> that this is probably as clear as mud so if you need more information
> please e-mail me at jmcbride5@cox.net .
>
> Thanks for all of the help. I'm very pleased with this site and the
> assistance that everyone has to offer.
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003


0
ken.wright (2489)
11/19/2003 9:35:25 PM
Reply:

Similar Artilces:

Formula calculating fulltime/parttime vs employees.
I have a spreadsheet listing employees jobs in one column. Another column lists if they are full time or part time. There are several employees with the same job but work different times. I need a formula to calculate how many people with that title work full time and how many people with the same job work part time. A pivot table will do a very nice job for you. They are very powerful once you get to know them. Take a look at Chip Pearson's site for a tutorial on them: http://www.cpearson.com/excel/pivots.htm -- Regards, Fred "VP" <VP@discussions.microsoft.com&...

Chart two variables with differing values
I'd like to chart data similar to: Year 2000 2001 2002 2003 2004 Thefts 75 120 95 101 80 Veh. Mfg. 100,200 125000 130000 145000 125000 I want to produce a chart with the year on the abcissa and with the Thefts scale on left ordinate and Veh. Mfg on the right ordinate and have the scales set accordingly. I'm running Excel 2002. Seems i can't select the Format/Selected Series data Series/Secondary Axis to set the scales correctly. I'd like to get som...

Excel Formula for determining azimuth relative to true north between two sets of geographic coordinates?
Using the geographic coordinates for each, I've "translated" the FCC procedure for determining the distance between two transmitters ( per FCC 73.208) into Excel formulas. Excel gives identical distance results to the "fill-in" form provided in www.fcc.gov/mb/audio/bickel/distance.html . But there is no comparable formula in the FCC R&R for determining the bearing (azimuth) between the stations relative to true north, although the FCC's fill-in form does provide azimuth. My problem with using the fill-in form is that I have hundreds of computations to make and...

HOW DO I ENTER DATA WHEN I SEEM TO BE STUCK IN A SUMMATION FORMULA
WHEN I OPEN MY SIMPLE SPREAD SHEET AND ATTEMPT TO FILL ONE CELL I AM STUCK THERE AND AS I TRY TO MOVE AROUND I GET THE BLUE SHADING AS IF I WERE DOING A SUMMATION. I CAN'T GET RID OF THE SHADING OR GET IT TO ENTER DATA? -- K J PETERSON hi typing with all caps is considered to be shouting and therefore impolite. press the F8 key once and see what happens. Regards FSt1 "kenneth j peterson" wrote: > WHEN I OPEN MY SIMPLE SPREAD SHEET AND ATTEMPT TO FILL ONE CELL I AM STUCK > THERE AND AS I TRY TO MOVE AROUND I GET THE BLUE SHADING AS IF I WERE DOING A > SUMMATION....

Formulas within Cell References
Hello All, I need to use a number that was calculated from a formula in a cel reference. Here is the situation: I have a large spreadsheet tha changes periodically. I need to count the number of items in tha spreadsheet(i will use COUNTA - already works) and then take tha number and use it as a row number in a cell reference like this: In cell H1: =COUNTA(A2:A9999) Then take that number in cell H1 and insert that into say $J$XXXX wher XXXX represents the number in cell H1. I'm looking to increase the calculation speed of the workbook by onl calculating cells that I need to. Thanks f...

Custom Fields/Formulas Receiving an error "NA" in Project Center
Hello All, Was wondering if I could get some help, we are running project server 2007 latest edition with all patches. And are running a custom formula. "IIf([Milestone]=Yes,"Milestone",IIf([PPC_Result]>=1,"100.00%",IIf([Baseline Start]=ProjDateValue("NA") Or [Baseline Finish]=ProjDateValue("NA") Or [Baseline Duration]=ProjDateValue("NA"),"No Baseline",IIf([Baseline Start]>Now(),"Baseline start in future",Format(([PPC_NUM]/[Baseline Duration]),"percent")))))" The formula runs off a ...

Formula to calculate interest only on a short term loan?
I am not familiar with Excel 2007 and need to calculate a bridge loan MONTHLY INTEREST ONLY scenario, with a fixed %rate, for a range of 1 -6 months. Not sure if if interest accrues daily or monthly. Assuming you have an annual interest rate, a month's interest is: =Principal*IntRate/12 This may be good enough for your purposes. Most financial institutions would calculate the interest based on the number of days in the month, so something like: =Principal*IntRate/365*day(eomonth(a1,0)) Regards, Fred. "Excel2007Help" <Excel2007Help@discussions.microsoft.c...

Two charttype on chart
Hi, I have export the data to excel from Vb, then add the graph in excel and set my secondary axis as.. ..SeriesCollection(4).AxisGroup = xlSecondary then how we set this group only as chatrtype as xlLine and the primary axis values by Column charttype -- Message posted via http://www.officekb.com Hi, Try this, ..SeriesCollection(4).AxisGroup = xlSecondary ..SeriesCollection(4).ChartType = xlLine Cheers Andy shah firdaous via OfficeKB.com wrote: > Hi, I have export the data to excel from Vb, then add the graph in excel > and set my secondary axis as.. > .SeriesCollection(4).Axi...

two servers for two domains
i have two domains, the first is 2000 and the second is NT. the exchange server is installed on NT. all the users (of the domain NT and 2000) are installed on a exchange server in the NT domain. i would like to have two servers exchange, one for the users on NT domain and the other for the users on 2000 domain, but with the same suffix (i mean "@microsoft.com") and with the server exchange on NT who goes on internet for the external email. how i can do this? thanks XCON: Sharing SMTP Address Spaces in Exchange 2000 PSS ID Number: 321721 cinqueg.nospam@yahoo.com wrote: > ...

Excel displays formulae, not results
I have inherited a spread sheet that has a number of formulae in it. However when I type in a new formula, it displays it as the formula, not as the result. This is not applicable to all cells, just some of them. I am now reduced to copying a formula that displays the result, then editing it to give the formula that I actually want. Obviously there is some setting in the individual cells that governs how it displays as well as the general setting. Hi Roger, This happens when a cell is formatted as text the moment -- Kind Regards, Niek Otten Microsoft MVP - Excel you enter the formula. C...

"nested" crosstabs?
Need help here! Have a PR database, with the following fields: EmpID, EmpName, EmpElig, S/H, Date, Hours, Type. I need a crosstab that may actually be a nested crosstab - dates for the columns (every day in a given month) and under each day's column, columns for "Type" - PR (production), NP (non-production), Hol, Vac, Sic, then hours should land under each type. An employee could have hours in more than one type for each day. Any suggestions on this? Thanks! ...

Custom addition formula
Using Office2000 Sp3 I use Excel a lot for historical things including eg; areas of land expressed in ACRES ROODS PERCHES which is not added in tens (similar to �/s/d). 40 perches = 1 Rood, 4 Roods = 1 Acre, 160 perches=1 acre. I have a formula which when applied to entries across 3 columns gives a decimal answer. The formula is =SUM(((D1*160))+(E1*40)+F1)/160 assuming acres are in D1, Roods E1, perches F1. Once this is decimalised I can work out ratios etc. However, I occasionally get tabbed text files or excel files from people who have entered lots of raw data as eg ARP where it would be ...

formula to project revenue
Hello, is there a formula I can use that will project revenue in real time based on total sales at any given time. Basically, take my sales to date, consider how many days into a calendar year we are at presently and make a prediction to where the sales will be at the end of the calendar year based on sales to date. So, say I am 50 days into the calendar year and my sales are 500,000. Presently, I am averaging 10,000 per day and at this rate my sales for the year will be 3,650,000. Is there a formula that will take the revenue from cell1 and provide the annual projected reven...

formula #60
Dear all, When I enter "=A2" for example into a sheet it stayes as =A2 instead of bringing the data. Can anyone explain this please? XP Pro SP2 Excel 2003 John North Yorkshire UK My guess is that the cell is formatted as text. Go to Format, Cells, Number. Under Category click General and then click OK. Then retype =A2. Hopefully this will fix it. "John Proud" wrote: > Dear all, > > When I enter "=A2" for example into a sheet it stayes as =A2 instead of > bringing the data. > > Can anyone explain this please? > > XP Pro SP2...

Counting checkboxes between two dates
I'm tring to build a query that will count the number of checkboxes between the dates of two other fields. I have two tables within the query. One table (tblInspection) contains InspectionID, InspectionDate, and Citation, among others. The other table (tblReinspection) contains ReinspectionID, and ReinspectionDate, among others. There is a one to many relationship between the two tables, tblInspection being the parent table. Only one InspectionDate will be entered but none or many ReinspectionDates may be entered. The Citation field is a checkbox. What I am tring to do is to find all the ...

Different formatting in a cell with a concatenated formula? #2
Yes, if a1 = blue, and b1 = red, and the formula in cell c1 is =a1&b1, want the result to look like this: bluere -- andy281 ----------------------------------------------------------------------- andy2812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596 View this thread: http://www.excelforum.com/showthread.php?threadid=27440 Formulas don't support this character by character formatting. If you can convert the results of the formula to values, you can do it though--but that destroys the formula. andy2812 wrote: > > Yes, if a1 = blue, and...

Add rows to a formula when copying
I have the following formula in cell C3 =IF((Calculations!C9-Calculations!C15)=0," n/a",Calculations!C3/(Calculations!C9-Calculations!C15)) I wish to copy this to cell C16 but wish to add more than the 13 row (ie 16 - 3) to each reference in the worksheet Calculations. I need i to add 27 rows. The end result formula I need in cell C16 is =IF((Calculations!C36-Calculations!C42)=0," n/a",Calculations!C30/(Calculations!C27-Calculations!C42)) Is there any easy way to do this? Happy to do it using a macro i necessary. To sum up, I would like to copy a formula and...

How can I match two spreadsheets by patients ID?
Hi there, This question is torchering me many days: I have two spreadsheets for the same patients. However these tables are different but complementary to each other. Both spreadsheets have unique Clinical Number. Is it possible to match the information from these two tables by Clinical number? Ex: Spreadsheet_1 ID Sample name Diagnosis Notes 123 RA234 ST RA Not for patient 456 RA456 TA GCA TA biopsy positive Spreadsheet_2 ID Date of Birth Sampling Date Investigator Treatment 123 10/01/1960 10/10/2005 Jo...

Print two sheets in Excel
Can I print two sheets in Excel 97? Thanks. Emece, If you mean print them as one print job (consecutive page numbers, etc), you can select the two sheets (use the Ctrl key when clicking the second sheet tab). Then use File - Print - Active sheets. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Emece" <anonymous@discussions.microsoft.com> wrote in message news:174ad01c448cc$16656ad0$a501280a@phx.gbl... > Can I print two sheets in Excel 97? > > Thanks. > > ...

Formula for cycle time
Hi, I need a formula to determine cycle time... actually to calculate the number of days from the date in one cell to the date in the other and put it in a third cell... thank you! JBell XL stores times as integer offsets from a base date, so you can use regular math functions: =A2 - A1 In article <1bf6e01c45232$5564ac40$a601280a@phx.gbl>, "JBell" <anonymous@discussions.microsoft.com> wrote: > Hi, > I need a formula to determine cycle time... actually to > calculate the number of days from the date in one cell to > the date in the other and p...

averages/if formulas-need help ASAP!!
I have two columns: one has initials of person completing the task, next to it is the percentage of work that is equal to. At the bottom, I am hoping to have two tables depicting the average % of work and total % of work for each person. I've tried =average(e3:e23 (if(d3:23="dj")) Basically I have no idea how to create a correspondence between the name and % of work...HELP! I'm so confused I can't even really explain my problem :) >I've tried =average(e3:e23 (if(d3:23="dj")) That's pretty close! Try it like this... Array ente...

Transposing Excel Formula
I have the following formula to calculate the probability of an option trading below price B3 Prob = =NORMSDIST((LN(B3/B2))/((B4/100)*SQRT(B5/365))) B3 = Target Price B2 = Stock Price B4 = Implied Volatility B5 = Days to Expiration I want to transpose the formula to solve for B3, assuming a value for Prob. Here's what I got, but it doesn't give the expected answer - did I make a mistake in these unfamiliar functions?: B3= EXP(NORMSINV(Prob))*((B4/100)*SQRT(B5/365))*B2 On Feb 7, 10:14=A0am, Steveal <steve_alm...@yahoo.com> wrote: > I have the following formula to calculat...

excel formula problem #3
i want to set up a system that counts a series of dates but only say how many cells are filled, i can get this to work generally but section needs to count 3 seperate dates but only add on any one of the (eg) cell A counts the number of dates in cells B-Z but with cells D,E, i dont want all to count, only one of the three even if all are filled but it must count if any of the three are filled hope this makes sens -- Message posted from http://www.ExcelForum.com Hi try: =COUNT(B1:C1,G1:Z1)+(COUNT(D1:F1)>0) -- Regards Frank Kabel Frankfurt, Germany > i want to set up a system that c...

Sum formula is not adding up properly
I am summing up hours in Excel and the sum formula is not working Properly. For 2 of my 5 cells are adding correctly, but the other 2 when added to the formula throw the entire thing off. They are all formatted the same in 13:33 format to measure the # of hours spent on an activity. What would the reason be that two of them are not working? (it is almost like exel is substracting hours when these 2 cells are added) No real problem. Your math is probably fine; formatting needs fixing. Select the cell with the sum in it. Format this cell as Time 37:30:55 -- Gary's Student &quo...

Strange behavior when copying formula.
Hi, Here is what I'm trying to do. I want to show the summation of the first two rows' number in the third row. I use the summation function to get the first result. Then, I copy and paste the result to the other two, but they show the same results as the first one. I check the formula of all three results, and they are all correct and referring to the right cells. Why is that? 1 2 3 1 2 3 2 2 2 Leo Do you have Tools/Options/Calculation set to Manual? If so, click the Automatic button. In article <eE#EeXFhDHA.2512@TK2MSFTNGP09.phx.gbl>, "Leo Leon...