Sum With Factors Built In

Jan 26, 2012

I have the data below. I need to total the amount in the USD Equivalent column but there is an exception. Where the "Curr Sold" and the "Curr Sold" are the same in reverse (as highlighted) in yellow, I need to take the net figure. For example below, I need to add the USD Equivalent column but then minus the 6,013,072.66 because the CHF - USD is the same in reverse with the same maturity date.

Maturity DateCurr SoldAmount SoldUSD EquivalentCurr Bought
09/03/2012USD4,000,000.004,000,000.00EUR09/03/2012USD3,500,000.003,500,000.00CAD
09/03/2012USD7,535,000.007,535,000.00CHF09/03/2012CHF5,578,989.356,013,072.66USD
09/03/2012USD2,500,000.002,500,000.00EUR09/03/2012USD2,500,000.002,500,000.00CAD
09/03/2012USD5,000,000.005,000,000.00AUD

View 1 Replies


ADVERTISEMENT

Interpolating Factors (factors For 1 Years 1 Month Early)

Jan 20, 2009

I have a spreadsheet with early retirement factors;

Years early
1 93.5%
2 87.6%
3 82.3%
4 77.4%

What I'd like to know is if there is an easy way to work out the factors for 1 yrs 1 mth early, 1 yrs 2 mths early, 1 yrs 3 mths early, etc.

View 2 Replies View Related

Ranking Depending On Several Factors?

Dec 7, 2012

I have to make a hierarchy of my employees, taking in consideration the achievemnt of their target. I'll attach a sample to make it simple to understand. The are 2 classes of factors: Class I and Class II.

- In Class I (witch counts 75% in total results) there are:
- New deposit - count 75% from class I
- Insurance - count 10% from class I
- Investments fund - count 15% from class I
- In Class II (witch counts 25% in total results) trere are:
- Loan A - count 15% from class II, but the number have a weight of 40% in results, and volume 60%
- Loan B - count 75% from class II, but the number have a weight of 75%, while volume have 25%
- New clients - count 10% from class II

Now, I have to rank the employees taking in consideration the Deviation for all of the factors above (columns E, H, K, P, Q, V, W, Z).

View 3 Replies View Related

Count All Of Factors Of Integer

Nov 4, 2011

I need to count, not list, all of the factors of an integer. For instance the number 12 has the following, 1 2 3 4 6 12, so the solution to the NoF(12)=6.

View 9 Replies View Related

Formula To Sum If 2 Different Factors Are Needed

Apr 9, 2014

I have a big list with names, money spent and date. I need somehow with a formula to do:

Take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname... Im gonna upload a photo.

Name
Money

Date

Daniel
5

01.03.2014

[Code] ........

I need the list of the left to produce me list of the right somehow, at least just the sum of everything spent everyday.

View 7 Replies View Related

How To Add To Custom Ribbon Tab That Is Built As Add-in

Nov 8, 2012

I am looking for the settings if possible that will add to a custom tab in the ribbon.

What I have is an add-in that creates a custom tab in the excel ribbon. I save it as an add-in and activate it in excel options so it is available for all workbooks.

Now I have a callback in there that runs a macro that opens a workbook, this workbook also has custom UI that I want additional callbacks added to the same custom tab created in the add-in.

Is this possible?

View 2 Replies View Related

Built-in Dialog Box Modifications

Dec 28, 2006

I need to modify 2 Excel built-in dialogs via VBA. Here is the first dialog box:

Application.Dialogs(xlDialogWorkbookCopy).Show

First, in the "Move or Copy" window, where it says, "move selected sheets to book:" I'd like the default selection to be the open workbook instead of "(new book)", which is the current default. How can this be done?

Second, I'd like to remove the check box so there is no option to copy visible, however, I need to add the code in the sub procedure to make a copy. How can I do this?

The second dialog box I need to modify is this one:

Application.Dialogs(xlDialogPageSetup).Show.

What I need this to do is open with the tab" Header/Footer" visible as a default, or, better still, have only the "Header/Footer" tab available and the other 3 tabs not there at all. How do I do this?

View 2 Replies View Related

Built-in Custom Function Not Udf

Feb 27, 2007

I am very proficient at Excel/VBA and have a question about custom/user-defined functions that may be a little more advanced. I understand how to write custom functions and access them through the user-defined functions menu, but I would like to be able to include my function in an add-in that users could simply add, and then access the function via the 'Fx' box at the top of Excel, like they would any other built-in function, instead of having to go to the user-defined functions menu. I guess what I am looking for is how to add 'built-in' functions and not user-defined ones. I just want the user to start typing '=customFunction(' and have the parameters pop-up in tool-tip form, like any Excel built-in function would.

View 9 Replies View Related

Code Of Vba Built-in Functions

Jan 26, 2008

I would like to export some of the functions available in Excel/VBA to some other languages (especially lookup functions, COUNTIF, etc.). Is it possible to find the code somewhere?

View 5 Replies View Related

Excel Calculation System With Two Factors

May 8, 2014

I am making an Excel calculation system where I have two factors (weight and opening length), which is essential for which lift there is possible to use.

There are 8 models each of which may have some limitations in terms of weight, and the opening length:

ModelPushing/pulling Force (KG)Stroke Length (mm)
HCV 130 350
HCV 230 600
HCV 330 800
HCV 430 1000
HCV 550 350
HCV 650 600
HCV 750 800
HCV 850 1000

What I am looking for is a formula, that when you enter numbers in two cells "Weight" and "Opening distance" of what their platform is to meet, then all the models that have the potential to accomplish this task pops up.

Example: A client writing a weight of = 40 kg and opening length of = 450 mm. The formula should then show which model that meets the requirements.

In this case: Models that meet the requirements for the lift.

ModelPushing/pulling Force (KG)Stroke Length (mm)
HCV 650 600
HCV 750 800
HCV 850 1000

View 5 Replies View Related

Calculating Cost Based On Several Factors

Jan 7, 2009

i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago.

I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information.

For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month.

Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month.

Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.

View 10 Replies View Related

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.

View 3 Replies View Related

Remove Data Based On Two Factors

Apr 16, 2009

In the enclosed SS there are several columns, "A" being Style and "G" being Available. I'm trying to piece together some code that will remove all of a style(all sizes) if the number of stock is less than say 3; however, if any one style has 3 or more available the entire style needs to remain in the spreadsheet.

I've been trying to piece together some code but I don't think I've quite come up with what I'm looking for.

View 5 Replies View Related

Prime Factors Regardless Of Number Length

Jan 29, 2010

Searching through the forums, I've found a post of daddylonglegs, witch shows how to find the smallest factor of a number:

View 7 Replies View Related

Create Name That Conflicts With Built-in Cell Name

Aug 15, 2012

I am attempting to create the Name "L1" for a list of cells but it conflicts with the built-in name for Cell L1. Is there any way around this if I need it to read "L1" exactly, and not "L_1" or a variation?

View 6 Replies View Related

How To Built Vertical Line Chart

Dec 2, 2009

I want to show values on the X axis, and show data/name/legend on the Y axis, then the line chart is vertical, not horizontal..

View 9 Replies View Related

Calculating A Range Of Values Based On 2 Factors

Feb 2, 2009

I need a way to calculate building rates based selecting the building and the value selected;

I have a list of building construction (fire resistive - protected, non combustible - protected etc)... in cell N8 and the limits in cell O8.

Now what I need to do is calculate the rates in the file attached (cells A5:J21 in my worksheet) based on the building construction and the value/limit I select.

View 6 Replies View Related

Producing Random Number, Then Another With No Common Factors

Aug 5, 2009

This is kind of an extension to a previously solved post. For a similar problem I used the attatched file (which someone from here so kindly came up with - but to be honest i dont fully understand),

but now im getting onto numbers from 1 to 100 so could get very confusing in the table!

RANDOM TABLE2.xlsx

Basically I want to produced a random number, (which I have managed) then another number, but it cant have any common factors as the first.

So 1st = 10, 2nd = 13 is acceptable
but 1st = 22, 2nd = 12 is not.

I have managed to do it with smaller values by just typing then out, but this is a very long winded technique, but is there a formula that can do this?

View 8 Replies View Related

Converting A Number Into A Word Depending On Several Factors (age, *** Etc)

Nov 4, 2009

I have a spreadsheet where I want to record the VO2max value (a number indicating the maximum volume of oxygen a person can utilise) of a person.

The VO2max is just a number, normally between 30-60.

I then need excel to look up the VO2max value (i.e. “35”) and give that number a predetermined inputted value name in a different cell (i.e. “low” or “fair”, “good”, “very good” etc etc).

However, as VO2max is also affected by age and *** I also have a column for the participants age and *** and I want excel to use that to make the value name.

E.g. in a 24 year old male a VO2max of 32 is “low”
But, in a 42 year old female a VO2max of 32 would need to be classed “”moderate”

Any ideas how this can be done? can it be done?

I could post the table containing the age range, values and names if I knew how to post an excel sheet on here.

View 9 Replies View Related

Index Function - Row Criteria Requires Two Factors

Mar 28, 2012

I was hoping to use the index function, but I'm having difficulty because the grid or table I'm pulling from, one of the row criteria requires two factors. It would be something like:

[A] / [B] / [C] / [D]
Numerical value / Dogs / Birds / Whales

1: 500-599 / Doberman / Parakeet / Grey
2: 400-499 / Rottweiler / Lovebird / Killer
3: 300-399 / Cocker Spaniel / Cockatoo / Baluga

So essentially, whenever a number falls within that range, it would pull the corresponding animal

450/Birds = Lovebird
320/Whales = Baluga

I wish I can show an excel spreadsheet on this, but not sure how.

=INDEX(ResultRange,MATCH(RowField,RowFieldRange,0),MATCH(ColumnField,ColumnFieldRange,0))

View 1 Replies View Related

Change Behavior Of A Built-In Toolbar Button

May 8, 2008

I am trying to assign a macro to the Print button on the Standard Toolbar. Basically I want that button to print the number of pages I've specified in a cell, but I still want to be able to use the 'File/Print...' option if I need to.

I did try to solve this issue in an earlier post, but this exact problem remained unsolved. For more information

http://www.excelforum.com/showthread...89#post1916589

View 13 Replies View Related

Pass A Built Array Back To Formula

Nov 11, 2009

I’m trying to send an array of values INTO a user defined function, do a little math on it, and then send the resulting array back to the caller. The caller in this case is an array formula in an excel cell.

I can get it to build an array after the math, but I can’t get it to pass the resulting array back to the formula. The following snippet is a simple version of the code. Here I am building the incoming array in the macro, but same difference at the end. The outgoing Oil_spgr variable never seems to contain the full, final array.

View 4 Replies View Related

Programmatically Built UserForm Doesn't Always Remove Itself?

Nov 7, 2011

I have wirtten a sub routine that builds a userForm dynamically based on user specific data. The form builds just fine and 9 times out of 10 the code at the end of the sub routine removes the form from the workbook. Since it's built on the fly I don't bother renaming the form. The times when it does not remove itself and the user restarts the program, part of the code written to the forms module references "UserForm1". and the the code halts. I know there is a way I can verify that UserForm1 has been removed and if not remove it before executing the next sub routine, I'm just tired and can't remember anything. Note that the UserForm1 contains CommandButtons that direct the user to Static UserForms that do not get removed, and those can trigger the sub routine to rebuild the dynamic UserForm.

View 3 Replies View Related

Sharing Workbooks With Macros Built In The File

Jan 15, 2014

Is there a way I can share the workbooks with macros built in the excel file?

View 1 Replies View Related

Automate Showing Built In Data Form

Jan 21, 2010

I recorded a macro to open the Form on the Data Menu. Sample Worksheet Attached. After starting the macro recorder I selected the Food table cell (located to the right on the worksheet). Then went to the Menu bar.... Data Forms... and Clicked new.... followed by close and then stopped the macro Recorder. It produced the following code.... which reflects a code error when run.

Sub McrEnterFood()
'
' McrEnterFood Macro
' Macro recorded 20/01/2010

Range("P1:U16").Select
ActiveSheet.ShowDataForm
End Sub

View 2 Replies View Related

Utilizing Built In Range Selection Dialog Box

Feb 25, 2007

Is there a built-in dialog box that allows the user to select a range by clicking and dragging with the mouse? I'd like to use this in my code and have the dialog box return either a range object or and address that I can use in a range assignment statement. I'm looking for something like what you get when you select Insert-->Name-->Define. The "Refers To" text box at the bottom automatically updates as the user clicks and drags across cells. Is there such a thing accessible via VBA?

View 2 Replies View Related

Modify Right-Click Menu Built-in Commands

Apr 17, 2008

I am developing a program for my electrical calculations (electrical panel). In the program, on my worksheet, I need all row inserts/deletes being watched and logged into another worksheet. I have become to a conclusion it would be best done by "taking over" default behaviour of built-in right-click menu commands like "Copy", "Paste" and "Delete".

In the ozgrid tips page I found some solutions how to deactivate those commands, but that is not what is best for me - when the menu item (or a corresponding key) is pressed, I would like to check if the ActiveCell (or ActiveRow) meats some criteria, and acording to that, make some changes in my "log" - and after - let further actions to default command behaviour. I think of it like "BeforeDelete", "BeforeInsert" or something. In fact the "log" is the worksheet with coordinates of my tables from the first worksheet, which should be changed according to new size of a table after the row insert or delete.

View 3 Replies View Related

Recognize Conversion (word To Number) And Multiplication Factors?

May 20, 2009

I'm trying to make a table of the total amount of a liquid used throughout the day. Here is what I am trying to do: In cell D4, I want to be able to enter something similar to the following: 3cup+2bottle+1liter

and by doing so, Excel can automatically recognize that 1cup is 8oz, 1bottle is 17oz, and 1liter is 34 oz because of the reference chart provided on the side. Also, it would be able to notice the 3, 2, and 1 amounts so it would multiply accordingly so it would know to do this: (3*8)+(2*17)+(1*34)

and then put the calculated amount in the cell. The correct answer should be 92oz. Is there a way for Excel to recognize the conversions (i.e. whenever it sees 'cup' it will multiply by 8) and multiplication factors (i.e. 3, 2, 1)?Is there a formula I can enter that I can just "drag" down to the upcoming days in column D?

I know I can just do something like this: (3*G4)+(2*G5)+(1*G6).

View 2 Replies View Related

Excel Query - Calculating Result Based On Three Possible Factors?

Jan 9, 2013

I am working with data and I am trying to identify people that have moved departments using the following criteria, a person are classed as a mover if their:-

Manager AND Dept ID have changed

OR Job Code has changed

OR Status has changed

I have been trying to come up with a formula but just can't get one to work?

View 6 Replies View Related

Obtaining Certain Data From A Range Depending On Multiple Factors

Nov 15, 2013

I have a sheet with 5000+ entries, column A is an ID number, column B has one of 4 outcomes C,D and E are just times and quantities and are not important.

In column A there could be multiple entries of the same ID number.

In column B it will show Pcid243A, Pcid243B, Ddar400A or Ddar400B.

I need to export (to another sheet) any entries with the following conditions:

A = ID number with any instance in B showing both a 243 and 400 condition.

E.G

A B C D E
AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E

In the example above, I'd need the red cells exporting to a new sheet as they meet the conditions of having the same ID and both a 243 & 400 outcome:

A B C D E
AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E

Now I've spent ages trying different things and can't get anything to work...

View 3 Replies View Related







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