I have a list of dates and the exchange rate at that time e.g.:
18/02/20051.449921/02/20051.451322/02/20051.441423/02/20051.444624/02/20051.4445525/02/20051.45228/02/20051.4508
What I need to insert are three formulas, 1 that works out the average for the last year, 1 for the year before that and 1 for between two given dates.
I recently moved to Central America and now am working on models that are either in $ or in the local currency (Q). I do not want to change my default currency value in the regional settings of the control panel because it is more convenient for me to stay in $ but it is a pain everytime I want to set a worksheet in Q and not in $.
So, I am wondering how I can change the default values in the currency drop-down list on the Home toolbar under Numbers in Excel 2010.
Currently, it gives me the change to apply the following currencies:
- $ English (US)
- £ English (UK)
- € Euro (€ 123)
- ¥ Chinese (PRC)
- fr. French (Switzerland)
Instead of either the £, ¥ or fr., I would like to have listed the Q, my other frequently used currency.
I've produced a workbook for my business where we can do automatic quotations based on a simple vlookup formula. We do however quote in a number of different currencies, ie Sterling, US dollars and the Euro. Is there anyway we can pick these conversions up by using data validation to convert the relevant cells or is there an other way?
View 2 Replies View RelatedI am mapping this file on the basis of a lot of fields. But the end product has 15 currencies. My requirement is to separate the accounts on the basis of the currencies and then consolidate it on the basis of GL account. For example, first I have to find all the accounts that are in USD and then if there are 2-3 similar GL accounts, then I will consolidate on the basis of the GL accounts. Please find attached file.
View 9 Replies View RelatedI am creating a simple spreadsheet to catalog some magazines. One of the columns shows the cover price which is okay for post 1970 magazines as it is in Pounds Sterling, but is there anyway I can get Excel to recognize Pounds/Shillings/Pence as currency. So put in "8d" for example without setting the field to a text format?
I want to be able to add up the total cost of some items based on their cover price but if I set the required cells to text format I cannot do this automatically using the Sum forumla.
Is it possible to have both Pounds Sterling and Pounds/Sillings/Pence as currencies in the same table or is using a text field for the latter the only option?
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedHi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
A$ SING $ Euro NOK UK US$
1.07001.25000.74186.10000.62081.0000
A 934.58
B 800.00
C 1,348.07
D 163.93
E 1,610.82
F 1,000.00
G 1,869.16
H 1,600.00
I 2,696.14
J 327.87
K 3,221.65
L 2,000.00
TOTAL 17,572.23
Values in column "H" (US$) have been divided by currency rates in Raw C2~G2 as we need the total in US $
But we also want to know the total amount of each currency used in column "H".
How to put a formula to find the total value of each currency used.
I can arrange for a new row to be automatically added to a spreadsheet after I have sorted the currencies in order?
So basically I am trying to record a macro that sorts the column into currency order then after the currencies have been sorted I would like an extra row added so that I can enter a total field in between....
I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.
Example:
Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May
I want to list the missing dates from this list.
Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.
What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc
I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.
im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.
I have a payment Excel sheet with thousands of lines Every line (one cell) has row payments data from mainframes like the following
Lhfskldhflshlshdflsd 234 234 sdljflsdjfkl 345.000,56 EUR lshflhshlfsld 2342 sdffgsd
Fsdlflsdf -384823 jsdlfsdlfjsdlfjldfjsldjf 43 50.000,56 EUR fsdgdfgdsfdg 23432
Jlflsdjflsjdfld 569 USD fjsldjflslfhsdlhdlshg 3-448 jlfsdkljfklsd
Etc.
Is there a way (UDF or Macro) to extract from every line(cell) (and put them in the cell to the right) these raw data only the amounts with the Currencies.
PS. (most of the times the amounts mentioned before are the biggest number in every line!)
I want to see if the people on list one are on list two. I need to come up with a formula that gives TRUE for each person on list one who is on list two and false for each person on list one who is not on list two. Several caveats: 1) there are people on list one who aren't on list two and vice versa, 2) the people have several defining characteristics which need to be met, and 3) dates are involved.
With respect to point three, TRUE should only appear if the people on list one are on list two and if they were employed by the organization on a specific date (ex: 3/5/1996); in order for the TRUE statement to appear in the G column, the person should not only be on list two but also be employed on a specific date. The specific date should fall within the start and end employment contract.
Is it possible to set conditional formatting to highlight any days which are saturday and sundays?
I have long long list of dates in the 17/12/2009 format, I need to be able to quickly see which dates are weekends.
I have a list of dates in this format (7/20/2013) that go through several months. I would like to make have a formula that takes all of the dates from a specific month out and make a new list of those days. For example, if I have 7/20/2013, 6/28/2013, 8/3/2013, 7/1/2013. I would like to to take only the dates from July and put them in a new list: 7/20/2013, 7/1/2013.
View 3 Replies View RelatedI have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.
For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).
I am also going to upload a data sheet, and an expected results sheet.
unique list.xlsx
I would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.
View 9 Replies View Relatedi am using excel 2003
i have a list of dates, which i want to color.
the even numbered months will have one background color
and the odd numbered months will another color
in the attached file, i have given a visual example.
If u create a drop down list with dates for eg;August 1st to Aug 31st , is it possible to save or link the date which i want to save some data in same excel sheet.
My idea is if i select august 1st in drop down list , the data which i saved in that date should display , same for the rest of all.
I'm looking to make a macro to search within a list of dates (In column A).
I need it to find the end of the year if it's 2007, 2006 and 2005, insert a column underneath and sum up column C, but only for that year. So for example, all the amounts in column C for only the year 2007 will be summed up in a blank row on column D before the 1st January 2008, and the same for previous years.
Then what needs to be done is for the year 2008, the same thing applies, but for each Month.
Is there any way to organize a list of various months and dates
First by creating a column of just the months, then by all of the dates, serperated by commas?
I am trying to write a formula for my account statement its got a list of dates of invoices descriptions then the value of invoice.
I want to check the dates of invoice to the date of statement if less the 1 day invoice total to go in a box called current, if between 2 and 30 days to invoice value to go into 1 to 30 days past due, if between 31 and 60 days invoice values to go into a box called 31 to 60 days past due, and then if dates are between 61 days or more then invoice totals go in to a over 61 days over due box.
I am currently assigning dates to a list manually by giving 5 dates on Mondays to Fridays. I currenly need to do this for around 500 rows and as you can imagine this takes some time.
Is there a way to have a formula that would assign for instance 5 dates on each weekday, purley by dragging the formula down?
For example....
ID
Date
a
07/30/12
b
07/30/12
[Code] ......
I want a list of working dates for the year in excel. Wanted to have it so that there was some flexibility in the table too, but cannot get it to work 100%.
For some reason in the below example, in December, 26th shows up (despite being in the list of holidays) and the 29th (Sunday) shows up too.
[URL] .....
Finding an average age from a list of dates. This is just a small sample:
7/9/2013
7/10/2013
7/10/2013
7/10/2013
7/11/2013
7/11/2013
7/14/2013
7/14/2013
7/19/2013
7/19/2013
I tried using =AVERAGE(DAY(A1:A10)) CTRL + SHIFT + ENTER, but that just doesn't seem correct.
i have two date lists
list 1 dates a1:a100
list 2 dates b2:b100
i want to extract the date from the two lists mentioned above in on list start from c2 taking in consideration the dates to be in sorting from A-Z when it extracted in the one list
i need formula not a macro
sorting list with date ranges with formula.
I have a list of dates and I want sort them by growth, also only to show the values after specific dates. I know how to do it with Macro, I just need list changing dynamically.
dim MyRange as range
dim Col as integer
Set MyRange = ActiveSheet.Range(2,1), Cells(102, 1))
if application.worksheetfunction.Match(ActiveSheet.Cells(5, col),MyRange,0) > 1 then
' it was in the list.....
else
'it is not....
end if
I imagine this isn't the best way and I since I couldn't get past the Set statement. What am I missing? Is the range of dates causing the problem? If my range is one cell in the range it sets the range?
how to find the 2nd, 3rd and 4th date from list of dates.....
View 9 Replies View RelatedI 've created a userform to search an excel database (that is populated by another userform) that contains a date field. This field is formatted as mmm/yyyy so I can search it by month.
All was well until out of nothing my sub can't find specific months.
It finds the related data for january, march, june, july and november, but not any of the other months.
In order to try and debug it I've used the exact same data in the other fields of the db changing only the date field to the desired month.
If I use excel's own Find, everything works as it should, the months are found just fine.