- MIN, MAX, MEDIAN for different categories
- Need help with formula to calculate min/max fields
- Formula for Calculating (Accrual) Off Time (PTO)
- Need formula for calculating hours in timesheet
- Formula to calculate min/max/average of filtered list
- Getting ERROR message for SUM & MIN/MAX formula
- Which formula is correct for calculating times?
- Formula for Calculating Forward Weeks Cover....
- Min, Max, Median Conditioned On String In Column Next To
- Formula for Calculating List Price using Discount Percentage and Net Price
- Formula for calculating tiered pricing
- Formula for calculating fixed monthly accruals by prorating based on start date
- Formula for calculating no of days worked
- Formula for calculating Variance
- Need help w/formula for calculating overtime hours
- Is there a formula for calculating dates 3 mos out
- Formula For Calculating %
- Formula for Food Ordering and Inventory
- Need help w/formula for calculating overtime hours
- Row Number for Min & Max Value
- Formula for Calculating Vacation Time Based on Anniversary AND Hours Worked
- Formula for calculating Variance
- Need math formula for calculating purchase price
- Custom formula for pivot tables

Column A Column B

Row 1 2 A

Row 2 5 B

Row 3 4 A

Row 4 7 B etc.

how can I write formulae to calculate MIN, MAX and MEDIAN for values in Column A corresponding to categories in Column B (e.g. MIN, MAX, MED for categories A & B respectively).

I don't want to have to sort and move around the data in the worksheet if I can help it.

I have a sales incentive for my team where they need to get 25,000 points. To get these points...each dollar they sell is worth a point, and every new collection is worth 3000 points. They need to have at least 10,000 points coming from sales.

I need a formula (or several formulas) to calculate how many points they still need to reach 25,000 based on how many points they have in the categories (sales & collections). I need to show that if they have zero collections, they'll need X# of sales, if they have 1 collection, they'll need X# of sales, etc.

For example...

If they enter in that they already have 4500 in sales points and 6000 in collection points (they sold 2 collections each worth 3000 points), then to reach 25,000 they would need to either do:

Option A: 0 collections and $14,500 in sales

Option B: 1 collection and $11,500 in sales

Option C: 2 collections and $8,500 in sales

Option D: 3 collections and $5,500 in sales

Option D: 4 collections and $5,500 in sales

Option E: 5 collections and $5,500 in sales

Where I get stuck is the 10,000 sales minimum...and I also don't want any negative numbers...so I haven't been able to figure out what to put as a formula if for example they have 30000 in collection points, but 2000 in sales. Technically, they have 25000 points, but they still need 8000 in sales since 10000 has to come from sales.

Can anyone help? Thanks so much...

I need to develop a tracking spreadsheet (for 18 employees) that will calculate accruing vacation time (on one worksheet) based on the following parameters:

Employees who have worked less than 1 year with the company:

- 10 days

Employees who have worked at least 1 year with the company:

- 12 days

Employees who have worked at least 2 year with the company:

- 13 days

Employees who have worked at least 3 year with the company:

- 14 days

Employees who have worked at least 4 year with the company:

- 15 days

Employees who have worked at least 5 year+ with the company:

- each year will add 1 day until reach the max 20 days

In addition I need to be able to deduct vacation time used. Does anyone have any suggestions for layout or for a formula that can do part of these functions? I appreciate any advice!

In my example (attached), you can see that the hours for 1/20 are over 8 hours, and 10 hours is showing in the "Total Regular Hours" column. I would like for all hours entered in this column to stop at 8, and any hours over 8 would show in the "Total Overtime Hours" column. (In this case, there should be an '8' in the "Total Regular Hours" column and a '2' in the "Total Overtime Hours" column.)

I've tried a few different options, but I can't figure out what the formula would be to achieve this result. Any help would be greatly appreciated!Timesheet Worksheet.xls

I have a Summary Sheet with calculations based on info contained within a Requests Sheet which contains details of correspondence received. Columns C:C on the Request sheet details the branch/section names and column CC:CC calculates the number of days taken to issue a response

I use this formula on the summary sheet to show minimum number of days to respond if the branch name is Finance:

{=MIN(IF(Requests!$C$2:$C$263=B1,Requests!$CC$2:$CC$263))} - this formula would be copied across a number of cells on the same row on the seperate sheet so B1 has the word Finance in it, c1 would have HR in it etc.

I use this formula on the summary sheet to calculate average number of days (excluding #Div/0 values) to respond if the branch name is Finance:

{=IF(ISERROR(AVERAGE(IF(Requests!$C$2:$C$263=B1,Requests!$CC$2:$CC$263))),"",(AVERAGE(IF(Requests!$C$2:$C$263=B1,Requests!$CC$2:$CC$263))))} - again this formula would be copied across a number of cells on the same row on the seperate sheet so B1 has the word Finance in it, c1 would have HR in it etc.

these formulas work fine, but what i now need is to calculate these figures if i filter column C:C in the Requests sheet. So if i filter column C just to show the Finance section then the calculation on the summary sheet for HR etc should show a mimimum and average of 0.

I tried this formula -

{=SUBTOTAL(5,Requests!$CC$2:$CC$263)*(Requests!$C$2:$C$263=B1)} to calculate the minimum but get a zero even if a filter hasnt been put on column C.

Can anyone suggest a correction? i would really appreciate it as i have been struggling with this one all morning

Thanks

Philip

I am getting an error when attempting to create this formula:

NBVC was nice enough to help me create the original formula, which was:

What I'm trying to do is take the lesser number of B14 or B23 to perform the calculation, rather than just take B14 as the value.

Any suggestions?

=IF(N31>O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25))

and

=IF(N32>O32,CEILING(("24:00"-N32+O32),7.25),MIN((O32-N32)*24,7.25))

where N and O are the start & finsh times respectively.

Both seem to calculate properly, but does anyone know which is the better formula to use...of does it matter ? Thanks.

Can someone guide me on how to use a formula to calculate the forward weeks cover (i.e. how many forward weeks cogs can fit into the month end inventory position).

Hopefully I've explained myself correctly.

Thanks in advance.

So basicly, what I need to do is to run through all the rows in column A and check if the string in the cell match my criteria (Ex. "ABN") and if so, then include the data in column B in my range and continue to the next cell. And afterwards calculate min, max, average and median.

Any help would be appreciated!

Fee is 1.00% of amount up to 1,000,000. The next 4,000,000 (ie 1,000,001 million thru 5 million) is charged at 0.70%, and the amounts over 5 million are charged at 0.60%.

So, for example if the figure is 8,000,000 then the fee would be:

1,000,000 at 1.00% = 10,000

4,000,000 at 0.70% = 28,000

3,000,000 at 0.60% = 18,000

Total 56,000

I want to write a formula that will reference an input cell such that if I input 8,000,000, the formula will calculate $56,000.

I have tried to do it with an IF formula and just can't make it work.

I'm trying to calculate a monthly leave accrual formula for staff on a monthwise table.

In the sample file attached, John has joined on 01-NOV-2010 so his leave accrual will start from Nov @ 2.5 per month. But I would like to have a formula that returns accrual as zero for the months prior to his joining.

Similarly, Mary has joined on 15-Dec-2010 so in the joining month of Dec2010 her leave accrual will be (2.5/31 x 17 days of service in Dec) and for the following months it will be 2.5 per month

Like Ali, who joined on 17-Feb-2011 wil have the zero accruals till the month of Jan and in Feb it will be (2.5/28 x 12days of service in Feb )

I hope the sample is clear. Any remote soultions to build on will be helpful.

Thanks

I need to create a formula that calculates the number of days a person has worked within a quarter, if they have also left the organisation within the same quarter.

For example, I have someone who left on 26/08/08. I need to establish how many working days this person actually worked within the quarter (01/07/08 - 30/09/08). I need to do this for a large number of staff, so would appreciate it if anybody can let me know whether there is a formula that would calculate this.

Many thanks!!

between planned miles and actual miles driven by drivers at several

different locations. I am given the miles per location daily. I need

to calculate weekly percentage per location. Then YTD percentage per

local, then total for all locals, then YTD for all locals. Can someone

help me?

--

Lori Lynn

------------------------------------------------------------------------

Lori Lynn's Profile: http://www.excelforum.com/member.php...o&userid=25565

View this thread: http://www.excelforum.com/showthread...hreadid=389974

data is entered. I am entering time "in" and "out" two times (first half of

day total, then second half of day total, after meal break) and then i have a

formula which calculates those totals. In algebraic terms, say: a+b=c, then

for the second half of the day: d+e=f. then c+f=h, to give total hours

worked. what i need to have is a formula that calculates a new cell (say,

"i") so that IF h is greater than or equal to a value of 8.0 (hours per day),

then it automatically calculates the amount of hours over 8.0, keeping the

regular hours worked 8.0 in cell "h", and calculating the overtime hours as

anything over 8.0 into new cell, "i". hope that makes sense? can anyone

help me? thank you!!!

What is the formula I would use for calculating dates 3 mos. out

ex.

April 17, 2006 July 17, 2006 October, 17, 2006

I'm new to the forum and not too hot with Excel either so please be patient with me.

I am after advise on creating a formula for creating percentages.

Basically this is for a chemicals spreadsheet.

What I have is a chemical - lets call it Chem 1

This will be made up from various raw materials so for maybe this example it's made up of 2 raws - let's say Raw 1 which is 60% of the finished product and Raw 2 which is 40%

So, what i need to be able to do is to enter into a cell my total order for Chem 1 in kg's, say 200kg and have it calculate into two seperate cells for the raws, how much i'm using of each raw (in this case 120kg & 80kg) and then to deduct it from a stock which would be pre-determined.

So, thank you very much indeed in advance for your help - hopefully the above makes sense.

Kind regards

Ian

___A________B___C____D____E_____F

Item Name__On Hand_____Par_____ Order

_________ Case_Each_Case_Each_______

Corn Flakes----0------1------1------0--------------

Baby Corn-------------4------1------0--------------

Croutons-------1------2------2------o--------------

I cannot figure this one out.

data is entered. I am entering time "in" and "out" two times (first half of

day total, then second half of day total, after meal break) and then i have a

formula which calculates those totals. In algebraic terms, say: a+b=c, then

for the second half of the day: d+e=f. then c+f=h, to give total hours

worked. what i need to have is a formula that calculates a new cell (say,

"i") so that IF h is greater than or equal to a value of 8.0 (hours per day),

then it automatically calculates the amount of hours over 8.0, keeping the

regular hours worked 8.0 in cell "h", and calculating the overtime hours as

anything over 8.0 into new cell, "i". hope that makes sense? can anyone

help me? thank you!!!

I'm still trying to come up with something that will return the row numbers for

the first and last row. I think I have found some code which will work once it's setup

right. I'm trying to combine two codes, where one returns the row number for the min value found in A and the other returns the row number for the max value found.

Sub FindMinAndMaxValue() Dim oRg As Range, iMin As Variant, iMax As Variant Set oRg = Cells 'Finding the minimum value iMax = Application.Max(oRg) iMin = Application.Min(oRg) 'Select cell containing the min & max value ( Need to combine statements to find both iMin and iMax in oRg) oRg.Find(What:=iMin, After:=oRg.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select oRg.Find(What:=iMax, After:=oRg.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select With Selection 'MsgBox "Max value : " & iMax & vbCrLf & "Cell position " & vbCrLf & "Row : " & .Row & vbCrLf & "Column : " & .Column MsgBox iMin & .Row & vbCrLf & iMax & .Row End With End SubWhen using a msg box, .Row returns the row value. I need to disinguish a .Row value for the Min & Max rows.

examples:

MnRow = .Row for iMin

McRow =. Row for iMax

Once I have my rows numbers distinguished then I can use them to define my startrow and lastrow.

Any help is appreciated.

Thanks,

BDB

I finally figured out the formula to calculate vacation time based on anniversary date for full-time employees.

Now, within the same sheet, I need to calculate vacation time based on anniversary date AND hours worked for part-time employees.

Here is the criteria:

0-3 years of service 1 hour for each 20 hours worked with a maximum of 4 hours

3-15 years of service 1 hour for each 13 hours worked with a maximum of 6 hours

15+ years of service 1 hour for each 20 hours worked with a maximum of 8 hours

Example: A part-time employee works 50 hours per pay period and his start date was 8/25/2002. Manually calculating, he would earn 3.8 biweekly

A part-time employee works 32 hours per pay period and his start date was 6/19/2011. He would earn 1.6 biweekly

Thanks!

Thanks in advance!

No luck finding an answer? You could always try Google.