Free Microsoft Excel 2013 Quick Reference

FORMULA FOR CALCULATING MIN MAX INVENTORY LEVELS

DOES ANY ONY HAVE THE FORMULA FOR CALCULATING MIN MAX LEVELS FOR INVENTORY
CONTROL


Post your answer or comment

comments powered by Disqus
If I have worksheet like this

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 apologize if this sounds confusing...I've been playing with formulas all day and can't seem to get this to work.

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...

Formula for Calculating (Accrual) Off Time (PTO)
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!

I am putting together a time sheet, and I am trying to figure out a formula for calculating overtime hours. Anything over 8 hours is considered overtime, and I would like those hours to appear in a different cell.

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

Hi

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

Hello all,

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?

I built a spreadsheet to calculate hours worked; I did it over a couple of weeks, reading and learning as I went. During the time I was doing this, somehow I ended up with 2 different formula for calculating hours worked...these are:

=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.

I need to calculate inventory forward weeks cover for the coming months. Based off of recent forecasts, I have the month end inventory positions and the Cost of Goods (cogs) by week also.

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.

I am trying to calculate min, max, average and median of a range, where the range need to be defined by a string in the column next to the data. If a given row contain a string like "ABN" then the data in the row should be included in the range and the same goes for the next rows. And so on for other strings.
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!

Hello, I have a net price (C4) from a supplier along with a percentage discount (D4) and want to calculate the starting list price. Can you help me with a formula for this? Thank you.

I am trying to write a formula that will calculate a price based upon a tiered pricing system. The tiering id this:

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.

Hi Guys,
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

Hi there

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!!

I need help with formulas to calculate the percentage of variance
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

i am trying to create a timesheet that calculates cells automatically when
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!!!

Here's an example

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

ex.

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

Hi folks,

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

I need help in creating a formula. I need a formula(s) where we input inventory on hand (cloumn B&C) then it compares to par levels we have identified to have on hand (columns D &E) then determines the amount needed to purchase (column F). In identifying par levels we do so by case and each. Listed below is the fist few columns of the spreadsheet. Corn Flakes case comes with 4 each, Baby Corn case is 6 each #10 cans, and Croutons case comes with 8 each 2# bags.

___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.

i am trying to create a timesheet that calculates cells automatically when
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!!!

Hi all,

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 Sub
When 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

Hello,

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!

I need help with formulas to calculate the percentage of variance 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?

I have a spreadsheet that calculates prices on a shopping list, such as price ea x quantity = purch price. I have some names for deals, such as B1G1F (buy one get one free), 50%, etc, which is factored into the formula for purchase price as needed. I am in need of a math formula (I can write out the formula in Excel) that would calculate a Buy One Get One 50% in BOTH a scenario with multiples of 2 quantity as well as a quantity of 1. The formula I have now works fine quantities of 2 and up. But if I have a quantity of 1 (there are reasons sometimes I need to use a 1 quantity), I am not getting the right answer. If I fix it to work on 1, it doesn't work on 2+. The basic math I'm using for the 2 and up is, (Quantity/2 x Price) + ((Quantity/2) x (Price/2)).

Thanks in advance!

Hi, I was wondering if there was a way to use a custom formula when using Pivot Tables. I want the formula to be Min/Max. I can get just Min and Max, but I would like the ratios between them. Any suggestion?


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