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!)

Distance Between Uk Postcodes

Hello,

I have two cells in Excel (A1, B1) with a UK postcode in each.

Are there any suggestions of how I can calculate the difference in Miles between the two postcodes with the resultant appearing in Cell C1?

Presumably this will require VBA to solve.

Your help would be appreciated

Regards

Pete


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







In Excel I have been trying to find an easier way to calculate a time
difference where the times cross midnight. Example:
Start time: 23:50:00
End time: 00:15:00

How would you formulate an equation to determine the duration of time or
differnce between the start and end time?



On my calculator, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get 462,534.50. Which is what I need it to be because I need to calculate the difference of a figure not dividing equally.

On excel, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get the SAME result of 462,534.05.

I have to account for the difference of not dividing equally so there is some format or accounting function that's preset that I don't want but I have no idea how to fix it or format my excel sheet to calculate as my calculator would.

Please can someone help? Thanks!


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


How do I calculate the number of batches per hour? Here's what I have so far:

Cell B5: start time: 4:15 formatted as 4:15:00 am
Cell B6: finish time: 6:15 formatted as 6:15:00 am
Cell B7: total time: 2:00 formula in cell: =TEXT(B6-B5,"h:mm")
Cell B8: total batches processed: 22 (this is entered manually)
Cell B9: batches per hour: formula in cell: =B8/TEXT(B7,"h")

as long as I have this formula in cell B9 the answer comes out correct which should be 11 per hour.
If the formula in B9 is B8/B7, the answer is 264.0, Is this because of the way excel is reading the total time or the total time is formatted as a time, not an actual number? Is this the correct way to solve the problem?
answer should be 11 per hour.


This formula is supposed to calculate difference between today and date hired to give years and months of service. Works fine for all except if the person was hired in year 2000 then this formula shows 110 y. Any one know how to fix this? or have a different formula that works.

=DATEDIF(E21,TODAY(),"y")&" y "&DATEDIF(E21,TODAY(),"ym")&" m"

Thanks
Rick


In Excel there is a difference between cells that are blank (= "") and that are empty. You can make blank cells empty by selecting them and pressing the delete key, but I need a fast way to do this on all blank cells in a large worksheet. The only way I have found is with a VBA macro that loops through every cell, tests for '.Value = "" ' and then uses the '.Clear()' function, but doing this on 30 columns x 10000 rows is far too slow. Any solutions?

(The reason I need to do this is for importing into Access, the database treats empty cells as NULL which is what I want. Blank (but not empty) cells screw the import process up.)


I need a formula to calculate overtime, after a work week of 40 hours.

for example: if an employee works 10 hours a day we would not count towards overtime until the employee completes a 40 hour work week.

Any suggestions would be greatly appreciated!!!!!

Thanks,
YV


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



Hey there,

I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.

Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.

I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.

I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.

Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.

I'll post up further comments as I am trying to work my way through it!

Thanks,

Jag


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


Sale = A1
Cost = B1
Margin = C1

I know to calculate Margin its' C1=1-(B1/A1) and to calculate Sale its, A1=B1/(1-C1), and I can do that in separate columns. But...... I'm tasked with trying to find a way so that I can do it in three columns. As in if I change cost then margin recalculates or if I change margin then sale recalculates. I'm thinking that this is going to cause a circular error but my boss says there's got to be a way to do it, figure it out. Any suggestions?

Alex


I have a spreadsheet where I need to calculate the increase in % one year over the other in column D for Eg if Profits are 95415 in Jan 2008 (Column B) and 80215 in Jan 2008 (Column C), then the % Increase will be (95415-80215)/80215 *100

I can calculate the formula for this , but the problem comes where for eg there is a loss in Jan 2008 for -95415 and there was a profit in Jan 2007 for 80215. How do I set up formula to take negatives & positives into account in calculating the increase or decrease/

Your assistance or anybody's on the forum will be greatly appreciated

Regards

Howard

http://www.mrexcel.com/forum/showthr...ghlight=howardneed


How can i solve a debugger error that states "run-time error '9'...Subscript out of range.

for a search box code provided by Joe Was




Hi there

I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:

Column M - Estimated Delivery dates
Column N - Actual Delivery dates
Column O - =IF(SUM(M2-N2)>0,1,0)

Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))

This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?

Any help much appreciated

Thanks,

James


Hello -

I am using Excel 2007. In prior versions of excel, when copying data, if I wanted to copy visible cells only, I would select "Go To, Special, Visible Cells Only" and then copy a range a cells. The default was always set to copy everything (including hidden cells), unless I specifically selected copy visible cells only.

In 2007, the default is somehow set to always copy only visible cells. Sometimes, I want to be able to copy all cells including those hidden but cannot seem to figure out how to swith this default option. I looked under Excel Options and did not see an option there.

I don't want to have to unhide and rehide everything each time i copy. I know i could do the hiding and unhiding via VBA but would prefer not to have to.

Thanks for your suggestions.


Hi Everyone,
I have searched online and in help but can't seem to find the best solution...

I have values like 00904BB303D6 that need to become: 00:90:4B:B3:03:D6 (the value is always 12 digits, and the : needs to appear every two digits.) Is there a formula that can easily do this for me? Your suggestions are greatly appreciated!

Thanks in advance!!!!


I need information on how to enter the proper syntax (formula) so that excel
can calculate overtime hours. In california over 8 hrs in one day is
overtime. The 8 would be considered regular hours and anything over is OT.



Is it possible to have numbers added to the same cell and have excel continue
to calculate the addition for me in that same cell......ex: I have the number
8 in cell d2 and I want to add the number 8 to that cell and have excel add
the 8 to the previous 8 for a total of 16 in the same cell.....the next time
I would add 5, and the total would be 21? Can this be done in a single cell?



Hi guys,

would really appreciate anyones help with this.

I have a column full of text-formatted fractions...

4/5
6/4
3/1
2/5
4/5

etc, etc.....the column is very long!!

I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.

If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!

Any suggestions on how I could speed this up?

thanks


Entering time values in custom format [h]:mm:ss
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).