Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

How To Plot Efficient Frontier

How to plot efficient frontier from sets of data containing expected return,
average return, variance, standard deviation, covariance and correlation ?



Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







I want to add data points to the scatter plot that are not part of the data
ie. add points to find their concentration on a standard curve.



Hey everyone,

I'm really hoping someone can help me with this...

I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?


Hello All

I wonder if you could help with this one, I have a list of data like this (no spaces or other characters)

JN551122B

What I need to do is split each of the characters in to its own cell

if JN551122B was in cell A1 I'd want to return:

B2 C2 D2 E2 F2 G2 H2 I2 J2
J N 5 5 1 1 2 2 B

The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc.

Thanks for your help!


I often use if statements to return empty cells, for example:

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a TRULY empty cell?

Thanks


I have a UserForm with several fields on it: 5 are 'required' and the other 6 are 'optional'. If a user doesn't enter a value for ALL of the 5 'required' fields, how do I get the code to return the user to the UserForm (with, I assume, the values they already entered, still showing)?

I have the AutoOpen code that starts things off, and then, in turn, loads the UserForm with the fields that need to be populated.

I can't figure out how to route the user back to the UserForm, to enter the 'missed' data.


I have a list of 5 buildings each with different unit counts, and an average price sold for each building. How can I get the average price of all units sold?

A B
312 $1000
54 $1010
35 $1835
32 $1028
53 $1937


I have data with time in standard fromat i.e. 3:00PM but I need it to be in
24 hour format i.e. 15:00. How can I do this?



I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average.

For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89.

What would the formula be to get the correct average time?


Is there a way to calculate the average days between a column of dates?

Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible?

Thank you in advance!
~cp


Hi

I have a table with various percentages, and I would like a formula to have an average of these. Please see attached file to get more of an idea.

Thanks
G


Good afternoon,

Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value ?

eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm.

Other than creating a table and using a vlookup function, I am hoping there is a better way?

Darren


Hello,

I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:

01/01/2007
02/01/2007
03/01/2007

I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.

Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!

Many thanks,
Caitlin


I have an excel worksheet that adds two other worksheets in a data
triangle. I copied it to create a new data set and used find &
replace to change the worksheet references to the new ones.

The cells still contain the result of the old formula referring to the
previous worksheets. The only way I can get the formula to return the
correct result is to edit (F2) each cell and press enter. Calc now
(F9) does nothing.

I've seen this before, but this time, I need to calculate many
thousands of cells and don't have time for this workaround.

Any ideas?

Thanks.

Don S



I need a formula to automatically calculate if the Job was completed in the
date range that was provided, and to return Early, Late or Ok accordingly.



Is there a way to use the new conditional formats (data bars, icon sets, etc) in PowerPoint? Basically, I'd like to create a table that shows icons based on data, like in Excel? I know I could paste a pre-made Excel table as a picture, but I'd like to avoid that if possible.

Thanks!


Hi,
How to calculate average handling time.


Good mornng - I am new to the forum - my name is Jena

My question is this - I have a large spreadsheet with multiple columns of information. I want to sort it by a certain column. I know how to do this. My question is, once I'm done with my work I want to sort it back to the original "sort" but I'm not sure how they have it sorted. I've tried to figure out how they have it sorted but can't. Is there some way to go back to the original sort?

Thank you for your anticipated help.

Jena

I am trying to establish a formula, but not too sure how.

For example " If Cell A5 has been marked with ' X ', then copy what is in Cell A6 to

cell C25 "

Hi folks,

Hoping someone can help me quickly as I'm pulling my hair out and have a deadline getting too close.

Been trailing the net and this board but can't work out how to do a simple variance.

How do I work out the difference between two numbers - including negatives.

If the numbers are all positive, it's fine as it's simply a case of A - B = C which is your difference. However, that doesn't work if B is negative.

I need a formula that would give the following:

First Number / Second Number / Difference
1 / 5 / 4
1 / -5 / -6
-1 / 5 / 6
-1 / -5 / -4
-5 / -1 / 4

Hopefully there's a simple function I've been missing.

Thanks in advance, R


Hi, can anyone help me with the below...

Im looking for a vlookup formula which will give me the maximum number from a list which contains multiple matches, i.e.

Lookup number 1 from column A, then give me the largest number from column B

example table:
A B
-----
1 5
2 2
1 11
3 2
4 5

the result would be 11

thanks all!