How To Get Name Related To The Price

May 10, 2013

I have a task to rank the stocks from the highest price to the lowest price. I've made a template and managed to get the ranking right. I used the LARGE() function.

But now the difficulty is to get the 'Stock name' related to the ranked price. I may do it with the MATCH() function. But the problem is there is an equal number 15%.

Can you have a look at the example attached here?

View 5 Replies


ADVERTISEMENT

Excel 2010 :: Multiplying Price By Variable Number Of Years And Annual Compounding Interest To Price

Sep 5, 2012

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))

Example:

If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.

Windows 7 Ultimate / Excel 2010

View 1 Replies View Related

Calculate The Implied Volatility Which Minimizes The Sum Of Squared Differences Between The Observed Market Price And The Model Price For Each Day

Jun 9, 2008

I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.

View 2 Replies View Related

Lookups For An Item's Price From A Price List

Jan 4, 2010

The analysis basically has 2 data components to it:
The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.

The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.

What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:

1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)

2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least)

3) Pull the price for this "closest quantity"

I have uploaded a worksheet showing the structure of that data.
[url]

Is there some VB code I need to do this, or can it just be a few simple formulas?

View 4 Replies View Related

Copy The Current Price Back To Sheet1. The Current Price Needs To Be Pasted Back Into Sheet1 (next To The Existing Price)

Jul 24, 2006

All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)

Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines
Sheet2 (has current material codes plus current pricing), has about 36000 lines

I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,

View 7 Replies View Related

Looking For The Closest Price To A Reference Price

Feb 12, 2010

I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price?

I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances.

I would also like to reference the source on the next column.

View 12 Replies View Related

If Statement: If The 2009 Price Is .50 Or Less Away From The 2008 Price, Bring Back "Check"

Mar 17, 2009

I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:

2008 2009
$23.95 $24.15

Using excel 2007

View 3 Replies View Related

Making Average Buy Price And Average Sell Price

Aug 13, 2008

to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.

View 9 Replies View Related

Sum Extended Price Without Extended Price Column

Jul 14, 2009

I have a unit price and a quantity. I want to be able to take the sum of the extended price without having to add a column for extended price. I don't want to just hide it, either.

Example attached.

View 2 Replies View Related

Lookup Related

Jul 18, 2006

I've tried LOOKUP, HLOOKUP, VLOOKUP, MATCH, SUMIF, ETC... Nothing working correctly. Here's what I need the function/formula to do: Find a # number value in column C, and then whatever text in another column ( range) I specify (like column A) corresponds with that same ROW. For example: VALUE: $40.00

1. Search column C3:C40 to look for $40.00- let's say it finds the value $40.00 in cell C21.

2. In cell A21 there is text "Gena's Sales".

I want the new cell the return a value of "Gena's Sales" because the $40.00 is in the same ROW as "Gena's Sales."

I think the reason I'm having such a problem is because it's numbers and text combined, and also LOOKUP does this "ascending order" thing that screws up the output. I'm getting values with LOOKUP from column A, just not the right ones. I don't need any ascending or descending orders. I just need to find the value in a range, then find the text in another range in the same ROW.

View 2 Replies View Related

Dynamic Related Lists

Aug 26, 2008

I have a vehicle intake-form in which users have to enter vehicle data.

I also have a huge list containing make/model information.

I would like to be able to use this database in the following way : I want the user to select the make in cell A1 , and then the model in cell B1. It must be possible to choose for example the make "BMW" in cell A1 and then cell B1 should only display the BMW models, and not all others that are present in the list...

I tried to use Data Validation with a list of choices, this works fine for the make (A1) ; but how do I make the list used for Data Validation on B1 dynamically related to the value in cell A1 !

View 7 Replies View Related

UserForm For Name Related Data

Aug 12, 2006

is it possible to set up a form (or any method) that would let me input a name (from my workbook) that would then "pull" up details about that name. I am thinking of making a staff holiday planner. This would have a column of names, holiday entitlement,days brought forward etc and columns that have a year (daily) calendar.

View 2 Replies View Related

Related List In Listbox

Jun 23, 2007

I need the listbox to have the related dates to the salesman I am selecting in Combox Not all dates

You can check my Attachment

View 4 Replies View Related

Filtering To Only First Date From A Set Of Related Records

Jul 30, 2013

I have a table in excel which looks something like this:

Product Ref
Date
Description

1
29/05/2013 19:58:50
Product A

[Code] ....

All I'm trying to do is get the only the first dates for each of the related Productsrefs. So the result should look like this:

Product Ref
Date
Description

1
28/05/2013 19:26:48
Product A

[Code] ....

Is there a built in function in excel that can achieve this?

View 3 Replies View Related

How To Group Values Related To Each Minute

Apr 16, 2014

I have a table in format below, I want show a third column with sum of w.r.t the minute, so for time 2:10 ,

i want to show 10 which is sum of first four values.

Started DB request
2:10:00 2
2:10:11 3
2:10:11 4
2:10:13 1
2:11:10 3
2:11:11 2
2:11:12 1
2:12:10 1
2:12:12 2
2:12:12 2
2:12:13 1
2:13:11 1
2:13:12 1

View 4 Replies View Related

If Formula - Look At All Eight Cells Related To These Courses?

Jun 19, 2014

got a formula that checked C4, then D4 for dates (either in or our of date) and returns a count of 0 or 1, with some cells having tet in them also.

The formula I am currently using is below

=IF( OR(F4 ="enrolled",F4 = "O/S"),0, IF(AND(G4="",TODAY()-F4>730),0,IF(AND(TODAY()-F4>730,TODAY()<G4),0,1)) )

I have a range of four courses (all child protection) that should be attended, some are eleanring, some local authority courses and staff should attend at least one but up to all four of them. Although they all fall under the question are they compliant or not (i.e.0 or 1)i do not want to count them all seperately. Is there any way I can look at all eight cells related to these courses and assess the latest date to assess if they are complinat (in date) or non compliant (out of date)?

I have attached the spreadsheet which I hope will clarify things

=IF(AND(E4="",TODAY()-D4>730),0,IF(AND(TODAY()-D4>730,TODAY()<E4),0,1))

View 8 Replies View Related

Correct Formula Related To Dates?

Aug 5, 2014

correct formula related to dates;

Assume cell A1 contains a date in DD-MMM-YYYY format. I would like to have a formula that looks in A1 and compares to the current date (TODAYS Date) and if the value in A1 is todays date or greater return "YES" and if not return "No".

View 2 Replies View Related

Look Up Value And Return All Related Values From Other Cells

Jan 8, 2009

I have two worksheets. One contains my master data and the other my look up table.

Master Data: ....

View 9 Replies View Related

Time Related To 24hr Clock

Jun 22, 2009

What i try to achieve is: from 00:00hrs to 24:00hrs = 24hrs which is 1.0 day
Have made the attached spredsheet to calculate it correctly to 1 day. But if you look on the attachment the "total hrs" is saying 00:00 (it transfers to 00:00 when i am putting in 24:00). Basically how am I able to make Excel to display 24:00 insted of 00:00. Maybe there is an add ins availible like the pop up calendar but for times instead.

View 4 Replies View Related

Fluctuating Status Related To Date

Nov 4, 2008

I need to create a formula that will calculate the status of a customer based upon an input code and the relevent date.

The attached workbook is very simplified but should help.

Sheet1 contains the date of input (col A) for the client (col B) plus a code in column C to help ascertain the initial status of the a/c.

In Sheet2 (col B) I need a formula or possibly a VBA solution that will create a commission status for clients with 'N' in column C of Sheet1 which is current for 8 weeks after the input date in Sheet1. Thereafter this status then need to show say an 'S' for standard commission.

This result will be linked to the date shown in B2 on Sheet2. Just to make it clear, I only need a letter as the result to trigger off the actual commission calculations.

View 14 Replies View Related

Array Formula To Find The First Related Value

Jun 29, 2009

(Thread: Max array with multiple columns) I got the following array formula:

{=MAX((A45:A47=A28)*(B45:D47))}

Here, where the value in A45:A47 is equal to A28, the maximum value is pulled from the same row, within cells B45:D47.

e.g.

A28 = Apples

A45 = Pears
A46 = Apples
A47 = Bananas

B46 = 10
C46 = 20
D46 = 23

The formula returns 23.

This is great. However, in order to control for a potential situation that will arise, I need to adjust this to find the first matching value within the data set. I do not know what the MAX should change to in order to find the first matching value.

Using the above example, if the data was now:

A45 = Apples
A46 = Pears
A47 = Apples

B46 = 10
C46 = 20
D46 = 23

How do you get the formula to return 10?

The position of "Apples" will change, hence the need for an array formula.

View 9 Replies View Related

Highlighting Cells Related To A Cell

Jul 14, 2009

in this sheet i have 3 groups of data. group 3 is the sum of group 2 - group 1.

how can i make the cells highlight that relate to the cell sum in group 3.

i.e when i click cell b38 cell b21 and b4 should highlight.

View 9 Replies View Related

Return Related Information From Another Workbook

Nov 4, 2009

we have an Invoice workbook "SampleInvoice.xls" with a drop-down list to select from a list of products, listed within the Data workbook "SampleData.xls" (which includes descriptions, codes, prices related to that product). So far, when you select the product, the macro will locate the product inside the Data Workbook, and return the Description of the product to the same cell in the drop-down cell.

My question is:
1) The cell to the left should bring up the Code for that product automatically
2) The cell to the right should display the Price. But there's a catch - there is a Distributor, Trade and Retail price. Ideally that cell will be a drop-down menu which gives the 3 pricing options. When you select the suitable field, the macro will determine the Code (as identified in point 1) and match the suitable pricing accordingly. Here is the code so far:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errHandler
If Target.Cells.Count > 1 Then Goto exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then Goto exitHandler
Application.EnableEvents = False
Target.Value = Workbooks("SampleData.xls").Worksheets("Stock List").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Workbooks("SampleData.xls").Worksheets("Stock List").Range("B1:B500"), 0), 0)
End If...........................

View 3 Replies View Related

Display Related Data From Chosen Value

Oct 31, 2006

Is there a way that I can click on a link in a worksheet that is linked to a data list in in another sheet and display the results in a text box on the original sheet containing the link.

For example ...

View 4 Replies View Related

Combobox To Show Only Values Related To Row

Feb 24, 2007

I have an inventory sheet that uses multiple userform comboboxes to input data to a sheet. Everything works great except on one userform "pullmat" where the user removes inventory from a unique list of all material available in "Master Log" sheet. The combobox that im trying to fix is "combobox3" in the "pullmat" userform. I'm trying to get that combobox to only show P.O. numbers that are related to the material selected in the "Material Code / Name- combobox2. In other words, the user should only be able to select a P.O.# that matches that specific material on the Master Log sheet.

The file size is too large to attach but I can email a copy if needed.

View 3 Replies View Related

Lookup Related Detail Using 2 Columns

Mar 5, 2008

I want to write a formula so that if an item in column A = " name" and column B = " date" (name and date to be selected from a drop-down box), then it will return the adjacent cells text in column C.

For example;
ColAColBColC
nick01-Marabc ltd
mick01-Mardeliver it
dan02-Mardiy
nick03-Margo ltd
mick04-Marfreebie
dan03-Marhelp ltd

If I select "nick" and "3/3/08", then the formula will return "go ltd". I know how to do filters, my aim is to create a master sheet and then for someone else to be able to select their name and a month. The month will then update a mocked up calendar (i.e. a 7 by 5 sheet where cell A1 = Date selected, A2 = Date+1, A3 = Date+2 etc). I've attached a basic mockup of what I want to do. If I select nick and march, the yellow highlighted cells should populate in C9 and E9 with abc ltd and go ltd, the others blank.

View 3 Replies View Related

Pull Related Data To Names

May 9, 2008

Essentially I have a sheet of names. This is just my master database. Other sheets get names from this sheet. The names are alphabetized. When I add a new name, the names are resorted on other sheets, but the data assocaiated with those names does not move.

Example:
Sheet1:
Jane Doe
John Doe

Add
Jimmy Doe

Therefore:..............

View 2 Replies View Related

Find Value And Copy Related Values

May 21, 2008

I am building a Macro which can be found underneath. The red code is not working right now and I am looking for alternatives to solve this error but until now I haven't found none.

Basically, I am looking for a correct code to copy files from a sheet to another sheet with a find macro.

Sub vinden()
Dim FindString As String
Dim Rng As Range
FindString = Range("A21")
If Trim(FindString) <> "" Then
With Sheets("Voorraadverloop").Range("A1:IV65536")

View 9 Replies View Related

How To Combine Multiple Rows Or Related Data Into One Row

Feb 22, 2012

I have a table with multiple rows for some employees and single rows for others. Each employee has a unique identifier.

Employee IDT1T2T3T4123Net salesmarket assetsmarginsspeed to market123Net salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time delivery180unit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter

Is there a way in which I can restructure the data so that the multiple rows for any single employee are added into subsequent columns in one single row? e.g.

Employee IDT1T2T3T4T1bT2bT3bT4b123Net salesmarket assetsmarginsspeed to marketNet salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time deliveryunit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter

The table has approximately 15000 rows & c. 30 columns in the original format. I can use basic excel, but I've never dipped into macros or anything very complicated...

View 1 Replies View Related

Event Trigger And Format Related Cells

Sep 20, 2012

Is it possible to write an event trigger macro to format cells? Let's say I want to add a new information to the row which is after FinalRow. Can I force excel to detect the input and then format that new row as previous rows or something custom?

View 4 Replies View Related







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