Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Calculate Ratio


I'm trying to create a quote... basically I need to find out about calculating ratios.

We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge.

I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Ratio With Decimals?
I am trying to get an exact ratio in excel but am unable to do so. Example:

60,000/58000 = 1.03. The ratio should then be 1.03:1. But excel is showing this is 1:1. Here is my formula

PHP
=TEXT(E55/E56,"0")&":1". I need it to show it to two decimals.

View Replies!   View Related
Employee Turnover Ratio Report
Need formula to calculate employee turnover ratio and if there is a place to get a free template to download?

View Replies!   View Related
Make A Ratio Of The Results Of One Column Against Another
can Excel make a Ratio of the results of one column against another ?

eg, Col A 2.00
Col B 1.00

in Col C, the ratio result is 2 : 1


View Replies!   View Related
Assign Values To Ratio Buttons
Assign values to ratio buttons

I have created a UserForm in Excel, to gather data for a questionnaire.

For each question, the user must select an answer from a radio button (Yes, No and N/A)

When I add the record (answers) to the sheet1, the results are shown as all being false.

I want to show either Yes or No or N/A


Private Sub CommandButton2_Click():

Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 1).Value = UserForm1.OptionButton1.Value
LastRow.Offset(1, 1).Value = UserForm1.OptionButton2.Value
LastRow.Offset(1, 1).Value = UserForm1.OptionButton3.Value

End Sub

View Replies!   View Related
Formula For Calculating A Ratio Using The Data In The Pivot Table.
I cant seem to work out the formula for calculating a ratio using the data in the pivot table.

Ive added a column next to the pivot table to work out the ratio between to columns.

=SUM(B11/C11)

But what if there is no data in cell (B11), I want to return a (blank space) but it returns a #DIV/0!


View Replies!   View Related
Grow Denominator Ratio By Fixed Percentage Until Equal Or Greater Than
I have what may be a math question as much as an Excel question. See my attached spreadsheet example in which I grow the denominator of a ratio by a fixed percentage until it equals or exceeds the numerator. This results in the denominator being grown X number of times.

View Replies!   View Related
Calculate IRR & NPV
i got a problem to calculate IRR and NPV for my company cash flow. i so confuse how to calculate cos the initial investment (expense) is pay in installment by yearly basis. i hope anyone can help me to solve this problem.

I m not sure whether what i'm doing is correct or not. thanx

http://spreadsheets.google.com/ccc?k...Hd2JSrIj7H-Pew

View Replies!   View Related
Sub Calculate
I am trying to call a sub calculate but I keep getting errors, is calculate a reserved sub name?

View Replies!   View Related
How To Calculate Age
to calculate the age from the format date of birth shown below.

SQL Data S1Date of Birth2Jun 9 1947 12:00AM3Jan 1 1957 12:00AM4Jan 1 1958 12:00AM5Jan 1 1956 12:00AM6Jun 4 1951 12:00AM7Dec 10 1963 12:00AM8Jun 17 1958 12:00AM Excel tables to the web >> Excel Jeanie HTML 4

View Replies!   View Related
Calculate The Value In The NPV
I have two columns in a payment schedule (which adjusts according to certain user inputs) that I need to use in my NPV calculation.

The first column is the Total Payment and the second is Inducements.

Therefore each value in the NPV calc. needs to be the sum of a given period's payment and inducement (but i don't want/have a separate column which calculates the sums). The number of periods adjusts with the users input of Term. There also may be periods where there is a payment but no inducement.


View Replies!   View Related
Calculate How Many Of The Same Value In A Row?
If I have this serie of values in the A column:

34
33
33
33
30
29
26
26
20
19
17
17

And want this results in the B column:

1
3
3
3
1
1
2
2
1
1
2
2

Those numbers will indicate how many of the same are in a row.

What's the easiest way to accomplish this?

View Replies!   View Related
Calculate Net Pay After Tax
building a spreadsheet. I have the total price in cell f6. In cell C6 I need price with no taxes. D6 should be the pst of 8% and E6 should be the gst of 5%.

View Replies!   View Related
How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..

-- got this numbers 1--2--3--4--5--6
-- want to calculate how much combinations of ( 2 ) numbers possible
-- want to calculate how much of ( 3 ) numbers possible
-- want to calculate how much of ( 4 ) numbers possible
-- want to calculate how much of ( 5) numbers possible

-- never 2 same numbers together (2-2) or (2-2-3) or (2-2-3-4) may not be in list

-- How can i make the result visible in kind of list ?


I would be nice if somebody knows a good solution..


View Replies!   View Related
Formula To Calculate Age
i am trying to build a formula to calculate a persons age.

I have tried using the DATEIF function but i keep getting a N/A


View Replies!   View Related
Calculate Variances
Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10.

Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this

View Replies!   View Related
VBA Calculate Pause
Hi everyone I am having a little trouble with a sheet i'm currently working on. Having exhausted what i believe to be every avenue trying to do this without VBA, i've finally had to sucomb to it as i can't see an alternative and i'm no VB expert by any means! This is what i have so far:

View Replies!   View Related
Calculate Attendance
I got struck in preparing absenteesm report from 22nd Sep 08 to 21st Oct 08. I've to put the dates on which a employee was absent ...

View Replies!   View Related
Calculate Totals
I have a spread sheet that is used to review calls placed by a call center.

Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call.

We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1-100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts?

My initial thought was something like this:

=SUMPRODUCT(--(A2:A5000=H2),(E2:E5000))

In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.

View Replies!   View Related
Calculate The Variance
i have a dynamic list of numbers....currently 10 numbers in the list.

how can i calculate the variance?

i have the upper limit (=MIN(1,(mean+half width))
i have the lower limit (=MAX(0,mean-half width)
i have the mean (avg of all numbers)
i have the t value (TINV(alpha, (n-1)))
i have the half width (t value * SQRT of Var/N)

i just don't know how to get the VAR/N


View Replies!   View Related
Function To Calculate IRR
My code below comes up with #VALUE! error msg


Function PIRR(NumPer As Single, StartVal As Double, EachVal As Double)
Dim Values() As Double
Values(0) = StartVal

For i = 1 To NumPer
Values(i) = EachVal
Next

PIRR = Application.WorksheetFunction.IRR(Values())

End Function

View Replies!   View Related
Calculate If There Is Anything In The Cell
Im wanting to do a sum calculate if there is anything in the cell.
Ive tried using the 'count' and 'count if' but both return values of zero.

There are words in the cell, rather than numbers.

I want it to return the value of cells with data in them, and exclude those with nothing ("") in them.


View Replies!   View Related
Calculate Button
I am trying to create a Radio button in a cell that calculates the entire workbook rather than pressing F9.

View Replies!   View Related
Calculate Age Down To Milliseconds
where i can get this download for Excel?


My husband sent me this program in Excel where you type in your birthdate and not only does it give you your age to the millisecond, but it also gives you a list of traits the person has. For example, I was born on November 20, 1987 and this popped up:

Age in years 19.61
Age in months 235
Age in days 7157
Age in hours 171760
Age in minutes 10305599
Age in seconds 618335921
Age in Milli seconds 61833592095
Age in weeks 50097
You born on Friday

View Replies!   View Related
Calculate Hours
I am designing a simple time card. Column D=time in, Column E=time out. My formula in column F to calculate total hours is =TEXT(E2-D2,"[h]hrs"). The result is not correct. Example: In at 9:30 out at 5:00 and the calculated total is -4 hours.

View Replies!   View Related
Calculate The Probabilities
How to calculate probabilities... say that I am buying pop bottles that say "1 in 6 wins" or drawing buttons from hats where 5 of them are numbered 1-5 and the other one is the winner. (Note that you would have to keep putting the buttons back after drawing so there is always 1-5 and a winner whenever you draw)

The possible combininations are easy to figure out... just do 6 to the power of how ever many bottles you buy or how many times you draw a button. So obviously if you draw once, there is 6^1 possible... if you draw twice there are 6^2 (or 36) possible combinations.

To figure out how many winning possibilities (win atleast once) there are it's not too hard at this level... you can just list the possibilities:

First - Second
1-1
1-2
1-3
1-4
1-5
1-W (1 winning combination)
2-1
2-2
2-3
2-4.................................

View Replies!   View Related
Calculate Overtime
I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates.

Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go.

Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x.

View Replies!   View Related
Add 2 Row After Each 9 Lines And Calculate A
I need a macro that add's after each 9 lines 2 new rows. The data I've it's not the same every month, one month I can have 27 lines the next one I can have 90 lines for exemple.

I need also to calculate in one of those added rows this "=B8+B4-B6". This calculation is to be apllied in each block of nines lines.

View Replies!   View Related
Calculate The Percentage
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file.

The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.


View Replies!   View Related
Formula To Calculate How Much Tax To Pay
Working from cell D19 I am trying to find a formula to calculate how much tax to pay.
I have a TAXABLE INCOME figure (in cell D18) , from this figure

The FIRST 1520 should be multiplied by 0.1 (in cell C19,)
The next 26880 should be multiplied by 0.22 (in cell C20, the answer to be put into D20)
Any remaining from the Taxable Income figure (put into cell C21), should be multiplied by 0.4 (the answer placed in cell D21)


View Replies!   View Related
Formula - How To Calculate For The 35 Min At Say 15.24
i just started with a company Royal Mail and they have some very strange timings for starting and finishing and i need a formula to help me work my money out and my hours to the precise min.

for example i have a shift comming up this week and my start time is down for 22:45 and my calculated finishing time is 07:23. i need a formula that will show on a spread sheet my exact time of work.

the other time i had was 22:35 to 06:35...it all seems prity simple to alot of people but this is my other problem. if i work from 06 - 18:00 i get one rate and if i work from 18 - 06:00 i get another rate.

so i need to know how to calculate for the 35 min at say 15.24 ph.

View Replies!   View Related
Calculate The Hours
How to calculate the Hours
Check the attached file

View Replies!   View Related
How To Calculate Time
WHen I was a beginner at Excel, I came up with the following formula to calculate the time difference between two "time" values. Since I was too lazy to add a ":" between my hour and minute, I had decided to simply enter the start (column B) and end time (column C) as a military time (e.g., 0100, 1230, 1500, 1930, 2300, 2359). The following formula would be in Column D.

=IF(C7

View Replies!   View Related
Calculate Retrospectively
I need to create a formula to work backwards from a closing balance to establish an inventory value on First In, First Out principles. The attached spreadsheet probably explains it better.

View Replies!   View Related
Calculate Age In Months
I need to be able to calculate age in months, and round whatever remainder to the middle of the month (.5). I am calculating z-scores and percentiles for in a childhood obesity project. The DoB and Date of measurements must be converted to read anything from 142.0 to 142.9 as 142.5.

This value will be used to look up the the appropriate LMS (Box Cox, Median, and Standard Deviation) in another excel table. I have to be able to compute age in months so 142.5, 143.5, 144.5, 144.5 etc. It is just not rounding off to the nearest half month.

View Replies!   View Related
How To Calculate Date
how to calculate the date for one year. If i have 24-Jun-08 (A1) i need to calculate 24-Jun-09. If i do =A1+365 it gives me an error (the little green thing in the corner of the box) is there a way to do this without the error showing up.

View Replies!   View Related
Calculate Total
I need a formula that will calculate the total of the items in column "W" when the row directly below it contains a "1" in column "V". It will need to calculate the totals from that row all the way up to the next row that contains a "1" in column V.

Example:

In X14 (where I will put the formula) it would total W9 through W14. However the formula still needs to be able to calculate the total if there are more or less cells to sum. So that the same formula could calculate that in X8 it will total W5 through W8.

View Replies!   View Related
Calculate The Date..
Is there a formula for calculating date for every 2nd thursday of the month excluding holidays?

View Replies!   View Related
Formula To Calculate A %
I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0
in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).

View Replies!   View Related
Calculate Text As Value
trying to calculate a text as value in a formula.

In cell A5 (please see the excel attached) I have a weighted formula that is supoosed to return a final result based on the drop down from cells B5 to V5. Drop downs give the option of choosing a value. Everything is going fine as long as you select a value (5, 3 or 0).

In cells H5, J5, K5, R5, S5, U5 there is also the option of choosing NA. I am trying to get the formula to calculate NA as being equal to 5. Basically, if for the respective parameter someone choses NA, the final result in cell A5 should not be influenced.

If all the parameters are 5 and only one has NA (any of the cells H5, J5, K5, R5, S5, U5), the final result (in cell A5) should be 100.

The same applies if all parameters are 5 & more than one cell or all the cells that have the NA options (H5, J5, K5, R5, S5, U5) are NA.

View Replies!   View Related
UDF To Calculate The Month
I am after a UDF to calculate the month based on a a date. I cannot use the month function in excel as the dates are slighlty different.

ie

05/01/07 - 06/02/07 = Jan
06/02/07 - 05/03/07 = Feb

View Replies!   View Related
Calculate Overlap
Here is my data

Range A1:L8

1234567891011Overlap111122275%22275%22250%22225%2220%2220%

I am trying to figure out a formula in L3 which is the calculation of overlap of region1 and region2. The top row is the number of days. What formula can I use to calculate the percent overlap? The numbers in column L show the expected result.

(Using XL 07)

View Replies!   View Related
How To Calculate Overtime
I am trying to formulate a formula that will calculate overtime hours worked.
Now standard hours are 17:30pm - 20:45pm. Anything outside these hours are overtime. If the start time is 18:00pm then the person is still paid from 17:30pm @ standard rate regardless.

Now I am trying to work out a formula that will cover hrs outside of the standard hrs AND hrs unworked but paid for.

see attached! September tab {blue highlighted cells}

View Replies!   View Related
Calculate Workbook
I'm opening 2 workbooks and I'm on Manual Calculation option. I wanted to calculate only workbook A. The standard button only give me the option of calculating a worksheet or all open workbooks (F9).

View Replies!   View Related
Calculate A Certain Weekday
I need to set up something that "calculates" an orders next delivery date. We deliver on Tuesdays, Wednesdays, and Thursdays for different stores. IE.

Store 1 - Today's Date-Wednesday 10/28/09 = Next delivery date is Tuesday 11/3/09.
Store 2 - Today's Date-Wednesday 10/28/09 = Next delivery date is Wednesday 11/4/09.
Store 3 - Today's Date-Wednesday 10/28/09 = Next delivery date is Thursday 10/29/09.

View Replies!   View Related
Calculate A Sheet Every Second
I am looking for a way to calculate a sheet every second. I haven't been able to find anything in the forums

View Replies!   View Related
Vba Calculate Certain Sheets
Is there a way to use vba so that you can turn off the calculation of certain sheet(s). I know you can do this with a button by cycling through the sheets and calculating them if they supposed to be calculated. If you need to know more detail or this just doesnt make sense just let me know and i will try to explain it better.

View Replies!   View Related
Calculate Sum By Week
I have a series of data in different columns, one of them is the date (daily, for like 5 years, exept weekends and national holidays), but I want to make another table with weekly data (which should be the sum of the daily data in a week, for each column). I thought there might exist any combination of formulas for that, to put once and drag it, and maybe it's pretty obvious, but even though I thought about it a lot, I couldn't find the solution.

View Replies!   View Related
Calculate All Possible Combinations
I have check list of 14games. With this I'd like to make combinations.

Each row has one default value for unchecked rows of 3 columns in the list.

View Replies!   View Related
If Cell Has Value Calculate
I am setting up a spread sheet to compare an invoice between 2 suppliers. It has formulas throughout and a total column. If no data is entered in cells then total column has "value" in it. How can I eliminate the"value" while still keeping formulas in place. Not all cells will have data in them on every instance. But I still need all formulas to be saved

View Replies!   View Related
Calculate Within Ranges
I have a range of cells and when an amount is entered into a cell I would like it to pick which row to pick from and do the sum from that row. for eg if I was to put $201,000 I want it to pick the row with $200,000 to $540,000 range and use the sum from that row. eg below

Taxable ValueRates Percent
$0-$200,000 $00.0%
$200,000-$540,000$2000.2%
$540,000-$900,000$8800.5%
$900,000-$1,190,000$2,6800.8%
$1,190,000-$1,620,000$5,0001.2%
$1,620,000-$2,700,000$10,1601.8%
$2,700,000-> $29,6003.0%

the sum I have for the rows is ((E3-A3)*D3+C3) but somehow need to have it choose from ranges and use appropriate sum.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved