Sum Function/Replace: Add The Costs Of Each Option

Oct 7, 2008

Plan numbers are contained in Row 1 (1518, etc). Options are listed in Column A (L101, L102, etc). I want to add the costs of each option, resulting in the total and each option code only listed once, with that total, below each plan. Example:.......

View 2 Replies


ADVERTISEMENT

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.

View 3 Replies View Related

Catching Errors When Using The Find / Replace Function / Replace All

Apr 20, 2006

Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.

View 3 Replies View Related

Using Replace Function To Replace ANY Date

May 22, 2013

I'm wanting to use Excel's built in replace function to replace ANY date with "Call:"

I'm not going to go into details about why, but I cannot use a code, as I only want to change them at specific times.

The dates are currently formatted as 12/09/2009. So I need to change the 12/09/2009 and any other date there may be to "call:" without having to go through every possible date.

View 9 Replies View Related

IF Function Syntax Option

Apr 30, 2009

Originally Posted by Syntax

IF(logical_test, [value_if_true], [value_if_false])

I noted today that in the helpfile topic for the IF() worksheet function it is possible to omit both the function's 2nd and 3rd arguments.

Although to do so you have to add a comma after the logical_test, eg:

=IF(A1="hello",)
When both [value_if_true] and [value_if_false] arguments are omitted the function always returns 0 if the condition is met or FALSE() if the condition is not met (unless the condition references an error value in which case an error is returned).

Note - I'm comfortable with leaving one of these arguments out, just can't think of a valid application for leaving both out. Just a feeling in the back of my mind that the fact that IF() in this format is able to return different data types (number vs logical depending on condition=TRUE vs FALSE) might mean that there could be a clever use for it?

View 9 Replies View Related

Replace Function In VBA

Apr 5, 2009

I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "-" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website:

[url]

Cells.Replace What:=Chr(63), Replacement:="-" 'finds and replaces the - that converted to boxes

I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.

View 9 Replies View Related

Replace With The Right Function

May 1, 2007

I was trying to help another user and came across this problem.

In cell A1 I have "abcd"
In cell A2 I have =REPLACE(A1,4,1,"e") resulting in "abce"
I tried to use = REPLACE(A1,RIGHT(A1,1),1,"e") but get #VALUE!
I am wanting to use RIGHT because the number of characters is unknown.

View 7 Replies View Related

Excel 2013 :: CF With 2 Option Buttons / Multiple Option Groups?

Sep 6, 2013

I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).

Excel 2013

View 1 Replies View Related

Replace #N/A In Match Function

Aug 1, 2007

=IF(ISNA(INDEX($D$3:$D$100,MATCH(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)),"",(INDEX($D$3:$D$100,MATC H(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)))

what i'm trying to do is to replace #N/A with blank. Taken the above from vlookup examples but does not seems to work.

View 9 Replies View Related

Replace A Forumula With A Vb Function

Jul 6, 2009

I've always used event driven functions - ie. user clicks a button and my function gets called.

What I want to do now is replace a HUGE formula that is impossible to debug with a function. So the idea is, any time the user changes any cell, the function should be called and update a certain cell with a new value.

View 9 Replies View Related

Looking For A Function To Replace Solver

Jan 11, 2010

See my file. I want to calcuate a % needed (20%) to add to a another material. Should be easy but I can not figure it out.

View 3 Replies View Related

Calculating Top 10 Costs

Jan 9, 2013

I have 3 columns

Column A is a list of suppliers
Column B is a list of purchase orders (there may be several purchase orders for each supplier)
Column C is a list of values

I am trying to show the top 10 suppliers in terms of value, not sure on the best way to go about it

View 3 Replies View Related

Allocating Costs Across A Row

Oct 27, 2008

I'm developing up a spreadsheet to conduct a feasibility on a 50 unit townhouse development and I was wondering if there was any way of allocating costs across the project without physically typing in the data in each month? (96 months in cashflow!)

ie: - In a row headed "Roads & Drains" can I allocate the say $500,000 worth of works across the months by using a formula or other methods?

My current method is very time consuming when I want to conduct a sensitivity analysis,

View 9 Replies View Related

Replace The Offset With Find Function?

Nov 25, 2012

I just want to replace the offset with find function. I have attached the sample file with the code. Just need a little change.

View 5 Replies View Related

Replace Part Of Longer Function

Mar 14, 2014

I am working with a lot of data and have been making a table of statistical analysis for several rock types. I have it worked out to where i can filter the data and with the use of a bunch of IF functions, below, and it keeps everything the way I want.

{=MAX(IF($H$2:$H$22="sedimentary rocks",$W$2:$W$22))}

The problem is I have to introduce new rock types, but if I just copy the formulas over i then have to go through hundreds of cells and switch sedimentary rocks to volcanic or whatever new class i have.

How this can be done faster. Maybe some way to say "find "text" within this range and replace" i can do it for actual values but when its embedded in a function im not sure.

View 2 Replies View Related

SUBSTITUTE Function To Replace Commas

Mar 2, 2007

I m using SUBSTITUTE function to replace commas with fullstop so I can multiply the end result with a number. But when I try to multiple for example B2 (0.1831) with 5, i get the VALUE! error.

Value Real Value (after substitute function)
0,1831 0.1831
23,3333 23.3333
12,5199 12.5199
5,5000 5.5000
20,5999 20.5999
24,4671 24.4671
200,0000200.0000
2,5386 2.5386
0,4000 0.4000
1,5019 1.5019

how I can resolve this so I can use the real values for computations (eg Real value *5), without having the VALUE! error message.

View 12 Replies View Related

Replace INDIRECT With INDEX Or Another Function

Jul 4, 2009

I am working on a spreadsheet which contains a number of reference data sheets (named “Reference data 2009”, “Reference data 2010” etc). As their names suggest, these sheets contain reference data applicable to the particular year. This reference data is used to perform various calculations in a “Calculations” sheet.
On the “Calculations” sheet, the user specifies the year for which they wish to perform calculations. At present, I am using the volatile INDIRECT function to perform various HLOOKUP calculations along the following lines:

=IF(D15>=HLOOKUP('Detailed net pay calculations'!D16, INDIRECT("'Reference data" & 'Detailed method'!$C$2 & 'Detailed net pay calculations'!D1 & "'!$B$43:$Y$52"), 4, FALSE), “Do something”, “Do something else”)

The reason I'm using the INDIRECT function is to identify the sheet with the appropriate year (hence "Reference data"&'Detailed method'!$C$2&'Detailed net pay calculations'!D1 which could be interpreted by Excel as "'Reference data 2009NEW" or "'Reference data 2010", depending on whether there's text in cell D1).

My query
The function I'm using is working perfectly fine but I am wondering if it is possible to replace the INDIRECT function (in red) with INDEX or another non-volatile function in order to reduce the performance impact (I have a fairly large number of these types of functions).

View 2 Replies View Related

Calculate Item Costs

Sep 7, 2008

I am trying to put together a sheet to calculate item cost after shipping and taxes. To be honest I may have complicated the whole thing but what I have is two
order volumes that need to be changed to calculate the most cost effective solution.
For example: Item 1 (a1) is $40 and item 2 (a2) is $10, if I was to order 100 (a3) of (a1) and 50 (a4) of (a2), then multiply them in (a5) and (a6) to give me total
order values without taxes etc. I then have (a7) which is the shipping (a8) the import tax and (a9) the VAT.
What I am stuck on is how to work out how much a1 and a2 costs me per item, if I was to order 100 of each then I think it would be simpler by calculating
the total cost and divide by 100 then again by 25% which is the cost difference between $40 and $10. So although I may have gone around the houses on this
one, I want to be able to change cells a3 and a4 and then see how much it is costing per item after shipping and taxes.

View 9 Replies View Related

Find And Replace Function - Change Text To Pop Out?

Sep 9, 2013

Here is the situation:
- Large block of text in one cell (1000+ words).
- I use the Find & Replace function (from the top menu) to find a specific word that may appear several times within the cell.
- Excel "highlights" the cell... but not the word specifically so I have to read all the text to find that word throughout the cell.

View 5 Replies View Related

Find, Replace Function: Formula Is Too Long

Oct 8, 2007

I'm receiving this message when I attempt to use the Find, Replace function. Formula is too long". I have a column of cells containing text only. ( about 2-3 paragraphs worth) I'm trying to replace a name with another name, which works fine where the cell contains a single or few sentences, but fails to replace when the cell contains too much information.

View 8 Replies View Related

Calculating Costs For Multiple Packages?

Mar 6, 2014

I am having trouble calculating multiple full packages in the attached spread sheet, at the moment the formula is multiplying the number of packages by the price of one full package instead of looking for the correct price for that number of packages. 2014 rates checker formula.xlsx

View 5 Replies View Related

Sum Costs Of Same Cell In All Sheets Of Workbook ?

Oct 29, 2008

Is there a possibility to make a sum off the amount of a cell (C59 in my example) of all the sheets in the workbook ?

just like =sum(sheet1!c59,sheet2!c59) but with all sheets.

View 4 Replies View Related

Net Present Worth Of Repetitive Costs?

Apr 12, 2013

I am going to convert different costs during life time of a product to Net Present Worth (NPW), consisting Maintenance and Rehabilitation costs (M&Rj) and Salvage Value (SV), according to the following formula: [URL] .......

SV occurs only once at the end of life time, it's NPW in Excel can be calculated as following:

=PV(i discount, AP,0, SV,1)

But M&Rj costs occurs J time during life cycle, and formulating manually their mathematical formula most times takes much time. Exactly what should I write in the cell to get their total NPW? In other words, how can I get the following formula by Excel functions: [URL] .......

Reminder: NPW= FV( 1/(1+i Discount))^n
FV: future value
i Discount: discount rate
n: numbers of years (periods)

View 1 Replies View Related

Set Up Spreadsheet To Record Business Costs?

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.

View 2 Replies View Related

Creating A Function Or Macro To Replace Data Between Cells.

Jun 2, 2009

I am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.

What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.

Is this possible?

View 6 Replies View Related

Find And Replace Function Using Cell References And Wildcards

Mar 29, 2012

Any way to use a find and replace function involving a cell reference. I have a spreadsheet with ton of junky text burying information that I want. Here is a simplified example

A B
Book 345353hg dgdgsdgfd fff Book belongs to Jim
Sneaker fdg4 Sneaker is worn by Jan
Gum dfg s d e te4345Gum tastes great
Cake jklsjflsjfjikCake smells delicious

The pattern is that the A1 text appears in the B2 text. What I want is

Find *A1
Replace with blank
Find *A2
Replace with blank
and etc.

So that in the end, I get this:

B2
belongs to Jim
is worn by Jan
tastes great
smells delicious

I've looked at the functions of find, replace, substitute, left, and right and I can't seem to find the right one to do what I need.

View 3 Replies View Related

Using FIND Function In Macro To Replace Value In Adjacent Cell?

Aug 10, 2012

I am using a macro to find a value in a cell and if found, replace the value in the cell to the left. Thr macro works fine - unless the value I am searching for does not exist. The answer is ( Ithink) an IF statement, but I am having problems with the errors, when I try the IF.

The macro below blows up at the ActiveCell.Offset(0, -1) = "109073X line with an error "Run-time error "1004" - Application-defines or object-defined error.

I've tried a number of things I've seen from this board but have not found a way past the error.

If the value is found, the macro moves to the else statment and stops at the ActiveCell statement with the above error. If the value is not found, then the macro goto NotFound.

Sub Macro2()
'
'Range("A1").Select 'Start from the home cell
Dim ValueFound As Object
Set ValueFound = Cells.Find(What:="Cirrus Reversals/CREDITS")
If ValueFound Is Nothing ThenGoTo NotFoundElseActiveCell.Offset(0, -1) = "109073X"'ActiveCell.FormulaR1C1 = "109073X"End If
NotFound:
End Sub

View 3 Replies View Related

Clean Function - Replace Special Character With Space?

Feb 28, 2013

I have special character that I removed with =CLEAN formula.

It was only one character which represents carriage return. It looks like one little square with question mark inside.

After I applied =CLEAN formula it disappeared, but now I don't have space between these two words.

How could I replace this special character with space?

View 9 Replies View Related

Formula To Spread Costs Between Months And Years?

Dec 3, 2013

See attached. (I added in some arrows for the first month so it is easier to follow the logic)

I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.

Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.

Essentially, I need the formula to return the correct amount that is being paid, even across different years.

I don't know if my formula can be modified or a new formula would be more efficient.

(This post originated from Commercial Services, which I just requested be removed from that forum)

View 3 Replies View Related

IF Formula For Real Estate Operating Costs

Oct 25, 2007

I am trying to set up an IF formula for real estate operating costs. I am guessing that this is going to work best with an IF statement.

(Sample of the spreadsheet is attached)

I know this is wrong but it expresses what I am trying to do.
=IF(C5="Gross",B10=following year of B9 with month/Day being 1/1), if not B10=the folling year of B9 with same month/day)

1Cell C5 can either be Gross or Net from a drop down list.
2Cell B9 lists 3/1/2008
3If cell C5 is gross, cell B10 should be the following year from cell B9 but with starting with 1/1/??? As the month and day(year start).
B11 would then be the following year from B10
(following month/day pattern).
4If Cell C5 is NET, cell B10 should be the following year from cell B9 using the same month and day 3/1/????

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved