# Conditional Formulas-to Get A Spreadsheet To Automate Calculations Of Unit Costs Based On Variable Packaging Names

Dec 1, 2008

I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.

I have a series of packages that are denoted by text phrases. Examples:

4/6/12
2/12/12
6/4/12
18/12
24/12

For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).

I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.

## Different Calculations Based On Variable Inputs

Feb 15, 2009

If I input any FX pair (Column B) without ???JPY in it, then the formula in Column K produces the correct result. Formula is: eg: If Column B = EURUSD pairs and the like: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*10000.

This formula will produce a +ve or -ve result in points (PIPS) based on the values of E3 and F3. (eg. E3 = 1.2800 & F3 = 1.2750. If C3 = L then the result is -50. Conversely if C3 = S then the result is 50.)

When I input the JPY pairs in Column B, I need the formula to calculate the same way but multiply by 100 (not 10,000). This is because all non JPY cross pairs have 4 decimal places and JPY cross pairs have 2 decimal places. (eg. 1 PIP in EURUSD = .0001, 1 PIP in USDJPY = .01).....

So if Column B = USDJPY the formula would need to be: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*100 . Have tried many combination's of the IF function but am now starting to think this is not quite the right formula for this situation. Attached is a sample of what I am trying to achieve. The formula would need to incorporate any/all variations in currency pairs as listed in the attached sample.

Jan 7, 2014

I am trying to set up a spreadsheet to record business costs, I have a column [A] and [B] with start times and finish times respectively. [C] is the shift length say 10 hours but I always get an error if its a night shift as the start time is invariably later than the finish time. Is there a way around this without inputting the dates into the cells as I am trying to not duplicate data entry.

## Spreadsheet With Unit Conversion Factors

Dec 19, 2009

I've been looking on the internet for a spreadsheet that lists the various units of measure with conversion factors to other units. I'm not looking for the formulas, just a chart.

## Automate Calculations On Timesheets (Overtime And Holidays)

Jun 26, 2014

I've been trying to automate the calculations on timesheets and having some trouble. I've attached a rough copy of part of the sheet to this post.

Here's where I'm struggling:

The timesheet has columns for Ins/Outs as well as Reg Hrs (Should never be more than 8), OT 1.5 (Standard 1.5 OT pay for anything over 40 in a week), OT 1.0 (More than 8 hours worked in a day but Vacation/Sick/Holiday/Funeral time keeps it below 40 hours actual work time that week) and then columns for Vacation, Sick, Etc.

Now I'm confident that my Regular Hours works with the following formula:

=IF((((C3-B3)+(E3-D3))*24)>=8,8,((C3-B3)+(E3-D3))*24)

If statement calculates how many hours worked, multiples it by 24 to put it in an hour format and checks whether it's greater than or equal to 8, if so then only show 8 (since any additional hours worked would need to be in the OT 1.5 or OT 1.0 category depending on the circumstances). If false, returns how many hours worked.

However, I am having some issues getting my OT 1.5 and OT 1.0 categories working correctly. The OT 1.5 column needs to check whether the actual worked hours is over 40 without Vacation/Sick/Funeral, etc included. The OT 1.0 column needs to show any additional hours worked beyond 8 each day but less than 40 over the whole week because of holiday/sick/vacation, etc.

So, if I worked 10 hours on Monday and there isn't any sort of paid leave time that week (and I work normal 8 hours the rest of the week), then Monday would show 8 hours in Regular worked and 2 hours in OT 1.5. Here's what I'm currently using for a formula (that isn't working...my latest addition was the OR statement):

=IF(AND((((C3-B3)+(E3-D3))*24)>8, OR((\$F\$12-\$K\$12)>40), \$K\$12=0), (((C3-B3)+(E3-D3))*24)-8, "")

If I worked 10 hours on Monday and there is some sort of Holiday (and I don't actually work over 40 hours that week not including the 8 hours of Holiday pay), then Monday should show 8 hours in Regular time and 2 hours in OT 1.0. Here's what I'm using for this formula (that also isn't working well and I hadn't started working with the OR statement here yet since OT 1.5 wasn't working) :

=IF(AND((((C3-B3)+(E3-D3))*24)>8, \$K\$12 > 0, \$F\$12<40), (((C3-B3)+(E3-D3))*24)-8, "")

testsheet.xlsx

## Calculating Project Costs With The Costs Different Each Month?

Jul 28, 2014

I'm dealing with Projects that have an X amount of Units used in a certain period of time. The amount of units are evenly spread among the time the project runs. The problem is that many projects start in 1 month and end in another.

To clarify, the code in my head is like this:

Total Units in project: 80

project starting on 12 jan, project ran 19 days in Jan.
Project ending on 14 feb, project ran 14 days in Feb.
Cost of a unit in Jan = 200

Ofcourse, the code needs to be smart, since the data will be filled in to a table, so the code has to change automatic every time a new project is entered. I cant use pivots 'cause of a standard format sheet.I have the costs per month in a raw data sheet. Start date and end dates in different cells next to each other.

## Conditional Formula - Calculate Only If Net Unit Is Zero

Jun 27, 2006

Here's the table for one game

Profit/Loss(\$)
xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)

Number in bracket means negative.

## means empty cell.

How can I type a formula, so that every time there's no stock left (ie zero net
unit), it will calculate the net profit once.

## Automate Formulas

Nov 17, 2008

i have 110 different columns. i want to do the same thing to each of these formulas (ie i want to calculate things like ln (A2 / A1)

is there some way i can automate this process so i only nhave to type in my formulas once and it will automatically enter in the same formulas for the other columns?

## Conditional Formatting Based On Defined Names

Jun 9, 2009

I would really appreciate if someone could help me with the following problem:

I wrote this code, but for some reason it doesn't work. I am completely new to VBA and have no idea what went wrong:

## Automate Formulas Modifications

Jan 9, 2007

As you can see in the attachment: worksheet: calculations. I make conditional sums in this sheet. After completing the 1st possibility of 1st condition with all possible 2nd and 3th conditions, I now need to make the same conditional sums for the second possibility of the 1st condition. So basically, I can copy past the formulas only "TV" does now need to become "MB" thats all that needs to change in the following 170 formulas. Is there a way to automate this proces, so that I do not need to do this manualy one at a time?

## Conditional Formatting Based On Names In One Column And Amounts In Another.

Jan 30, 2010

For my example, in column B I have a list of guest names. These names will repeat based on their entries.

In column C I have amounts next to their name.

If the total of all amounts next to their name equal \$1,000 or more I want all the rows that their name is in to be highlighted.

I attached an example.

## Automate A Web Query Whereby Names On A List

Nov 22, 2005

how to automate a web query whereby names on a list in Excel are pasted into a website’s text box search and the returned data is copied into Excel? I have a list of racehorses that I need data for which is available at: http://www.drf.com/workoutHorseSearch.do

## Macro Or VBA To Provide Conditional Formatting Based On Formulas

Mar 13, 2014

Is it possible to record a macro or write VBA to apply conditional formatting to certain columns of cells where there are exceptions/conditions for the different types of conditional formatting? If possible, can those exceptions/conditions be based on a specific type of formula?

See my DATA EXAMPLE picture linked below. On that example, Columns K-Q need conditional formatting but manager wants row 38 to be blue based on the fact that it is using a =SUM() formula. This represents one "part" out of 75 that could be on a given sheet, each "part" has a different number of sub-parts that are used. So I can't just highlight columns K-Q because of the occasional SUM row that needs to be blue.

The conditional formatting that I am currently using in Columns K-Q only apply to K3:Q37 and not to row 38 at all. I currently have conditional formatting on columns K-N, P-Q where when '=ISNUMBER(xx)' returns TRUE is white and when it returns FALSE is light orange. Column O uses '=ISTEXT(xx)' for the same colors. Manager doesn't want to copy paste that formatting and wants it setup as a Macro/VBA.

DATA EXAMPLE - [URL]

RELEVANT INFORMATION

Columns K-Q need conditional formattingColumn K - Formula pasted in only on lines that require the Sales Price to show, will return a number value or error

(=VLOOKUP(B38,'SaleWS'!C:G,5,0).Column L - Formula pasted in every cell in column except L38 is '=IF(Hxx="G",IF(Exx"Description EX",VLOOKUP(\$Dxx&\$Gxx,'PriceWS'!\$D:\$F,3,0),""),"")'.

L38 has a sum of all above valuesColumn M - Formula pasted in every cell in column except M38 is '=IF(ISNUMBER(Lxx),Ixx*Lxx,"")'.

M38 has a sum of all above valuesColumn N - Formula pasted in every cell in column except N38 is '=IF(Exx="Description EX",VLOOKUP(Dxx,'Material'!A:O,15,0),"")'.

N38 has a sum of all above valuesColumn O - No formulas here, just manual entryColumn P - Formula pasted in every cell in column except P38 is '=IF(H38="P",VLOOKUP(\$Dxx&\$Oxx,'PressWS'!\$A:\$L,12,0),"")'.

P38 has a sum of all above valuesColumn Q - Formula pasted in every cell in column except Q38 is '=IF(H37="W",VLOOKUP(\$Dxx,'WeldWS'!\$A:\$F,6,0),IF(Hxx="T",VLOOKUP(\$Dxx,'WeldWS'!\$A:\$F,6,0),""))'.

Q38 has a sum of all above values.Rows where they are using SUM for all the rows above for that part need to be blue

## Calculate Phone Call Costs Based On Rate Per Second

Dec 20, 2007

I have downloaded .csv files with my phone call costs. I want to compare phone companies, so I need to calculate the h:mm:ss amounts to \$ based on flagfall and rate per 30 seconds. I can open the .csv files in Excel but I do not know anything about Excel.

## Automate Marker Options In Spreadsheet?

Jul 2, 2014

I have a scatter plot in which there are, for example, twenty plotted points representing one of three major categories.

We're using a red square, green triangle and blue circle to represent our categories on the graph. Going in to every plot point and manually changing each point so that it has the proper color and style is very time consuming.

Is there a way to designate in the spreadsheet (in its own column or something) that I always want the plot point to be represented in a certain way (like a red square) when graphed?

## Automate The Copying Of Formulas And Cell Formatting

Oct 31, 2009

is it possible, after inserting new sheet rows, to automate the copying of formulas and cell formatting into the newly created space, instead of manually copying the formulas and formatting down into each column?

## Lookup Price Per Unit Based On Quantity Sold

Nov 22, 2007

I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.

Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.

## Formulas Return Wrong Calculations

Mar 16, 2012

Formulas are not working fine in VBA, They return wrong calculations. Here is the piece of code.

Range("S13").Select
Selection.FormulaR1C1 = "=SUM(COUNTIFS(C[-13],{""Closed"",""Open"",""Answered""},C[-9],{""Site from CDA"";""Site from LDR""},C[-11],""System""))"
'strr2 = Range("S13").Value
Range("R13").Value = strr1
Range("T13").Select

[Code] ........

## SUMPRODUCT Formulas Slowing Down Calculations

Feb 22, 2008

I am doing a report which analyzes a specific person's transaction per country (3 criterias). The workbook has 3 sheets. First sheet is the database, 2nd is the report itself and 3rd is a reference table. I am using a sumproduct formula to count per person's transaction per country. It is giving me slow calculations.

I cannot possibly use a pivot table since there will still be other calculations involved after determining the transactions. I tried using database functions but i can't seem to understand how to go about it when there are multiple criterias involved.

I am attaching a sample file for your reference.

## How To Save Results Of Calculations On New Spreadsheet

Dec 2, 2013

I have a spreadsheet in which I do calculations, I need to do them many times and want to record on another spreadsheet the results of two cells. I would like to show the results as a string of values.

## Using Conditional Formatting To Fill Certain Cells Based On Variable?

May 23, 2013

The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.

## Array Formulas Causing Slow Calculations

Dec 24, 2007

I have an Excel file that has a certain amount of Array Formulas. It takes 30 seconds to calculate every time the user makes a change. Some users do not have the Status Bar showing, and they think that Excel is "locked up". I want to make a user form that will appear when the Stats Bar is "Calculating..." There are a lot of "progress indicator" tutorials on this site, but they all seem to relate to VBA code being inserted in between certain lines. That doesn't apply. My Excel file has no VBA. It just takes a long time to recalculate. I want a VBA form to appear, that will match the % progress shown on the Status Bar, whenever a change is made to the spreadsheet.

## Dragging Formulas On Same Sheet (automatic Calculations Turned On)

Dec 10, 2012

I have a huge spreadsheet requiring me to manually enter alot of fields with formulas. I am using the formula

=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))

The full formula is quite long as this pick up the data off over 300 work sheets. (there's probably an easier way to do this aswell) When I copy or drag this to another cell with on the same worksheet it gives me the correct formular with the correct cells changes as I want. However this formular does not work. I have auto calculate on and I manually press enter on the cells and still nothing. If I change the following for the entire formula (about 8min to do each cell) it works

=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))

Is there an easier alternative then manually changing each number?

## Variable Commission Rate Calculations?

Mar 29, 2012

I need a way to calculate commission for my sales reps. The commission is based off a calculated value in H39 of a sheet that I have developed.

They get 7.5% of the value when between \$1 - \$100,000, example: sales of \$50,000 = \$3,750 in commission.

If the value in H39 is > \$100,000 but \$250,000 but \$500,000 they get 7.5% on the first \$100,000 plus 5% on the amount between \$100,001 and \$250,000 plus 3% on the remainder between \$250,001 and \$500,000 plus 2% on any amount >\$500,000 example: sales of \$800,000 = \$7,500 (7.5% on first \$100,00) plus \$7,500 (5% on the next \$150,000) plus \$7,500 (3% on the next \$250,000) plus \$6,000 (2% on the sales >\$500,000 for a total of \$28,500.

## Variable Number For Use In Formula Calculations

Jun 19, 2008

I have a formula that will be used by others to obtain a certain result by altering a number in the formula. What I would like to do is make that number obvious as the one that can be changed. The formula reads - "=(M48+P48+Q48)*0.08453-(((J48+K48)*\$K\$4)*0.1)" -what I would like to do is color the 0.08453 in the Formula Bar so it stands out.

## Conditional Formatting With Date Calculations?

Sep 30, 2013

I have two date columns named "start_date" and "Dead_line".

Both the columns are in the date format. Ex: 9/14/13 9:14 AM

i want the third column to be the "status" column.

if sysdate-dead_line < 30 % of (dead_line - start_date), then the status column should become green. if sysdate-dead_line is between 30 % and 70 % of (dead_line - start_date), then the status column should become amber. if sysdate-dead_line > 70% of (dead_line - start_date), then the status column should become red.

First the dead_line - start_date to be converted to hours, then have to do conditional formatting like mentioned above.

## Conditional Calculations & Display Of Cells

Nov 20, 2007

Conditional Calculations & Display Of Cells. (See attachment)

## Conditional Formating :: Exclude N/A Cells From Calculations

Dec 4, 2006

I have a single column of data that is, perhaps, 100 rows long.

I have ticks, crosses and 'N/A' in various cells within this column. There are no cells that are blank.

I want to count the number of ticks within the column and want to be able to say:

If the number of ticks is = 100% then it's green.
If the number is >75% and <100% then it's amber.
If the number <75% then it's red.

I also want to exclude the N/A cells from the % calculation.

## Change Event For Conditional Formatting Not Firing On Formula Calculations

Sep 25, 2007

I have used the code for formatting: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm. This works fine when typing in the numbers manualy but if I have a formula (eg:A1=b1+c1) and a1 = 15 nothing happens. Is there an update button or a better way to do this?

## Excel 2010 :: How To Reference Unit Price In List Two Based On Date In List One

Mar 8, 2012

I'm using Excel 2010 on a Windows 7 machine.

I have one sheet with a large list of dates and total prices.

I have a second sheet with a list of dates and unit prices.

I want to divide the total price in my first list by the unit price on my second list.

I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.

My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.