Jump to content
Sign in to follow this  
workinglikeadog

Microsoft Excel help

Recommended Posts

Mjainoz    1,029

Lol. I started reading this and it reminded me how much I have forgotten, but also how much I don't know. Trannies is a funny site .... Just when I'm bored and not training there is always something in Sandbox to amuse. ;).

Share this post


Link to post
Share on other sites
skel    214

Hello fellow excel geeks......seeking some help. I have spent a few hours trying to work out this formula with no luck (doing my head in basically).....



Trying to use a date of birth to workout when the student would start Prep.



So, a date could be 04/10/12 (dates are entered as dd/mm/yy), and I need a formula for the following



If (DOB) if >= to 01/07/11 to <=31/06/12 = Prep 2016


If (DOB) if >= to 01/07/12 to <=31/06/13 = Prep 2017


If (DOB) if >= to 01/07/13 to <=31/06/14 = Prep 2018


If (DOB) if >= to 01/07/14 to <=31/06/15 = Prep 2017


Share this post


Link to post
Share on other sites
tortoise    820

Skel, set up a lookup table starting in A2 in my example.

 

 

DOB

Enters

1/07/11

Prep 2016

1/07/12

Prep 2017

1/07/13

Prep 2018

1/07/14

Prep 2019

1/07/15

Prep 2020

1/07/16

Prep 2021

 

Then set up a formula that reads the DOB and looks up the DOB in A13:

 

=VLOOKUP(A13,$A$2:$B$7,2)

 

My little test results:

 

 

DOB

Prep entry

5/07/11

Prep 2016

5/06/12

Prep 2016

5/07/12

Prep 2017

5/06/13

Prep 2017

5/07/13

Prep 2018

5/06/14

Prep 2018

 

If it doesn't work, it's because the dates are being read as TXT inputs, not DATE inputs. PM me if this happens and I'll give you a fix. It involves one formula.

Edited:

AARRGHHH!!! formatting of my neat tables disappeared, but I hope you get the idea. The lookup table has the date in ColA, the entry year in ColB.

Edited by tortoise

Share this post


Link to post
Share on other sites
XCom    34

or perhaps something along these lines...

 

=CONCATENATE("Prep ",IF(A1>=DATEVALUE("1-JULY-2014"),"2019",IF(A1>=DATEVALUE("1-JULY-2013"),"2018",IF(A1>=DATEVALUE("1-JULY-2012"),"2017",IF(A1>=DATEVALUE("1-JULY-2011"),"2016","*UNKOWN*")))))

 

Assuming a column of DOB dates (formatted as date) and a column with 'Prep" calculation.

 

Where A1 is the cellref of the first date (cell must be formatted as a date) and the formula is entered into another cell on the same row.

 

Then you can just copy the formula down the column, and it will update the references.

 

Basically just IF() that falls back through the 4 start dates.

 

Note: I assumed "Prep 2019".

Share this post


Link to post
Share on other sites
skel    214

Many thanks - did a copy an paste XCom and it worked perfectly - I will take it to work tomorrow and plug it into the larger spread sheet.

 

This new job is giving me an opportunity to extend my "limited" skills, but I like a challenge.

 

:-

Share this post


Link to post
Share on other sites
XCom    34

There is probably a more generic way to do it - as it's essentially looks like the financial-year of a date + 4 ?

 

Assuming that is the case, then perhaps something like this might work ??

 

Used the same way as the previous one.

 

=IF(ISERROR(DATEVALUE(TEXT(A1,"d/m/yyyy"))),"Unkown",CONCATENATE("Prep ", YEAR(A1) + IF(MONTH(A1)>=7,1,0) + 4))

 

Basically, says if it's a valid date, get the year, add 1 if the month is >= 7, and then add 4

Edited by XCom

Share this post


Link to post
Share on other sites
tortoise    820

I hate auditing nested ifs and I avoid them like the plague because they can be so error prone. Like I hate auditing spreadsheets from peeps who don't know sumproduct.

Share this post


Link to post
Share on other sites
XCom    34

I hate auditing nested ifs and I avoid them like the plague because they can be so error prone. Like I hate auditing spreadsheets from peeps who don't know sumproduct.

 

Yes, the first solution was just a quick & dirty, which implemented the specified conditions - it's nasty, ugly, and would need to be updated as new years are added, but it does give the required answer.

 

The 2nd solution is better IMO, as (provided the logic is correct) it should work for any year... and the only purpose of the IF() in that one is for empty references.

Share this post


Link to post
Share on other sites
tortoise    820

Yours is really obviously a programmer's solution to an excel problem - totally not a criticism, just an observation that it is code-think.

 

Having managed unsophisticated users for a long time, I prefer to teach them stuff that they can pick up and use for other purposes. Lookups are a fantastic and much under-rated spreadsheet tool, because they are simple (if you understand the principle of exact or inexact matches - for most applications I just tell people they must add FALSE and they eventually work out why), they are AUDITABLE by just about anyone, and very hard to get wrong. Plus if you add MATCH and INDIRECT to the toolkit, you are really humming.

 

Just personal preference, but I use IFERROR to return for nuls or invalid calculations rather than IF(ISERROR.....

 

I also reject spreadsheets that are given to me for audit where macros have been used to do stuff and stamp values. I don't mind that once the process has been vetted properly as it speeds up subequent analysis and reduces file size, but if you have to sign off on stuff that might land you in court, you need a rock solid audit trail.

 

Geek time is over, back to some real work with my 60MB spreadsheet.

Edited by tortoise

Share this post


Link to post
Share on other sites
XCom    34

Yours is really obviously a programmer's solution to an excel problem - totally not a criticism, just an observation that it is code-think.

 

Having managed unsophisticated users for a long time, I prefer to teach them stuff that they can pick up and use for other purposes. Lookups are a fantastic and much under-rated spreadsheet tool, because they are simple (if you understand the principle of exact or inexact matches - for most applications I just tell people they must add FALSE and they eventually work out why), they are AUDITABLE by just about anyone, and very hard to get wrong. Plus if you add MATCH and INDIRECT to the toolkit, you are really humming.

 

Yes, it's funny how a spreadsheet can have a difference in philosophical approach: accounting vs. programming.

 

I'm a programmer and always approach things from that direction, but I constantly have to 'negotiate' a middle ground between what seems perfectly logical and more elegant to me, and what is practical and usable for accounts/finance who requested a solution - more often than not, their 'clunky' solution gets implemented, because at least everyone can understand it - LOL.

 

Just personal preference, but I use IFERROR to return for nuls or invalid calculations rather than IF(ISERROR.....

 

I'm not sure that IFERROR would work in this case, as it only returns a single argument: IFERROR(value, return_if_error)

 

Basically, the 2nd solution is a True/False return based on the ISERROR test for a valid date-value

 

IF(ISERROR(value), return_if_error, return_if_ok) where the return_if_ok is the calculated value

 

IF(ISERROR(DATEVALUE(TEXT(A1,"d/m/yyyy"))), "Unkown", CONCATENATE("Prep ", YEAR(A1) + IF(MONTH(A1)>=7,1,0) + 4))

Edited by XCom

Share this post


Link to post
Share on other sites
Pete    276

Love excel - not quite the same proficiency as Xcom but I was using it recently to validate some maths for an android app This one will find the distance in km between two GPS coordinates (in lat/long)

 

=ACOS(COS(RADIANS(90-Latitude_1))*COS(RADIANS(90-Latitude_2))+SIN(RADIANS(90-Latitude_1))*SIN(RADIANS(90-Latitude_2))*COS(RADIANS(Longitude_1-Longitude_2)))*RadKilom (where RadKilom=6371)

 

and this one will give you the heading in degrees between the two

 

=DEGREES(ATAN2(COS(RADIANS(Latitude_1))*SIN(RADIANS(Latitude_2)) -SIN(RADIANS(Latitude_1))*COS(RADIANS(Latitude_2))*COS(RADIANS(Longitude_2-Longitude_1)),SIN(RADIANS(Longitude_2-Longitude_1))*COS(RADIANS(Latitude_2))))

 

 

Just perfect for a real time bike tracking algorithm or "find my car" type app. :)

Share this post


Link to post
Share on other sites
XCom    34

Love excel - not quite the same proficiency as Xcom

 

Proficient? - HAH! - tell him he's deamin'

 

I do consider myself to be proficient in multiple programming languages - all the usual imperative and declarative suspects, including the various Web 'languages' - but I use Excel (functions/macros) only when I get a request for an Excel solution, and otherwise avoid it like the plague.

 

However, because Excel uses what could be loosely described as a rudimentary functional programming language, I find it easy enough to initially write the problem out in pseudo-language, and then go looking for the required Excel functions to substitute - or write a macro if I need to.

Share this post


Link to post
Share on other sites
Pete    276

 

Proficient? - HAH! - tell him he's deamin'

 

I do consider myself to be proficient in multiple programming languages - all the usual imperative and declarative suspects, including the various Web 'languages' - but I use Excel (functions/macros) only when I get a request for an Excel solution, and otherwise avoid it like the plague.

 

However, because Excel uses what could be loosely described as a rudimentary functional programming language, I find it easy enough to initially write the problem out in pseudo-language, and then go looking for the required Excel functions to substitute - or write a macro if I need to.

I'd love to be proficient in ANY language LOL My current passion is embedded controllers and IOT so I am having a crack at a heap of languages. platforms and environments (all with varying degrees of success :( ) but I get hardware and maths pretty well at least :)

Share this post


Link to post
Share on other sites
skel    214

Seeking yet more wisdom.....

 

Trying to calculate =IF formula for three options (using age as the pre-cursor decision). I can make it work with two options, but not a third J Just need the number of FTE not the wording FTE. So, looking at the following..........

 

If aged 2.5 to 3 = 3 (FTE)

 

If aged 3 to 4 = 4 (FTE)

 

If aged 4 = 5 (FTE)

Share this post


Link to post
Share on other sites
XCom    34

Basically, the way nested IF() functions work is:

 

=IF(test_condition, true_return, false_return)

 

or another way of saying it is:

 

=IF(condition, then, else)

 

So, to nest multiple conditions you simply substitute the else with another IF(condition, then, else) and so on and so on, and close all the nested IF functions at the end, Which excel conveniently colour-codes for you.

 

So.... something like this might work... but obviously you need to fine-tune the references and perhaps the logic.

 

=IF(age < 2.5, "error", IF(age < 3, 3, IF(age < 4, 4, 5)))

Edited by XCom

Share this post


Link to post
Share on other sites
Alex Simmons    494

Seeking yet more wisdom.....

 

Trying to calculate =IF formula for three options (using age as the pre-cursor decision). I can make it work with two options, but not a third J Just need the number of FTE not the wording FTE. So, looking at the following..........

 

If aged 2.5 to 3 = 3 (FTE)

 

If aged 3 to 4 = 4 (FTE)

 

If aged 4 = 5 (FTE)

 

Rather than nested IFs, could use a lookup table instead:

 

Screen%20Shot%202015-07-29%20at%204.23.0

 

 

You can also define range names, e.g. select all of column A and define it with than name "Age", and select the lookup table in D3:E6 and define it to be named "FTP_LookupTable".

 

Then the formula in column 2 can become a bit easier to read:

 

=VLOOKUP(Age,FTE_LookupTable,2,TRUE)

 

Just copy down the formula.

 

What this does is look at the age value in column A, check that value in the first column of the lookup table, then returns the corresponding value in the second column of the lookup table.

 

When an age value doesn't match exactly what's in the lookup table, it will choose the maximum value not exceeding the value being checked. In effect the table creates ranges.

 

It's much neater than nested IFs, and especially if later you need to adjust the lookup table with modified ranges. It's a simple table change rather than getting messed up with nested IFs.

Share this post


Link to post
Share on other sites
tortoise    820

Alex, that was my solution to Skel's first query. I'm a BIG user of lookups for all the reasons you outline. Even lower-skilled users can adapt them once given an example. I steer clear of names ranges because they are harder to audit and can trap the inexperienced end user.

 

In this case CEILING would work as well, if real age was in decimals of years. It will depend on how the original data is structured. If you have DOB and a date on which you are measuring the age, just subtract DOB from the reference date and divide by 365, the formula will look something like this:

 

=CEILING((basedate-DOB)/365)

 

Will return an integer that matches Skel's list.

Share this post


Link to post
Share on other sites
FatPom    2,147

OK, here's a query that's so technical it will BLOW YOUR MIND :shy:

 

MY WFH environment is a Win laptop with 2007 Office. I run a 2nd 21" monitor which is great for looking at contracts in detail and side by side comparos. I run everything from the laptop and use the 'Extend' function in Display to drag pretty much anything I want across to the external monitor. When I say nearly everything, except XLS that is. If I open with the laptop I cannot drag it across and even if open it from an application that is displayed on the external monitor (like Outlook for example) the XLS itself always 'jumps' back to laptop.

 

This doesn't happen PPT, Word or anything else.

Share this post


Link to post
Share on other sites
Tyno    1,322

Can you resize the Excel window down and then drag it across FP?

Or does it not move, no matter what?

Share this post


Link to post
Share on other sites
FatPom    2,147

Can you resize the Excel window down and then drag it across FP?

Or does it not move, no matter what?

 

 

That's how I used to do it Tyno, but it jumps back no matter nowadays, been doing it for about a week. I've probably hit some whacky setting when I've been rushing from one meeting to another. :shy:

Share this post


Link to post
Share on other sites
tortoise    820

I can't do that between my excel in parallels on the Mac and the secondary screen either. But on some dual screen machines in my clients office I can, and on some I can't. Seeing as I play find the desk on a daily basis, I'm darned if I know why the difference.

 

Edit, because I re-read your problem. On my Mac I can place excel in either window, but not stretch it across both.

Edited by tortoise

Share this post


Link to post
Share on other sites
Ex-Hasbeen    3,740

My turn to beg for help this time.

 

I have a large list of addresses that I need to break into street, Suburb & State. The formatting is consistent, with a comma between the fields I need, the only issue is that many have a sub-address as well which can have 1 or 2 commas in it. The sub-address is to be included in the street address. I can work out how to split the text string from the left using the commas, but if I do that, then some addresses will get broken into 4 or 5 fields rather than 3. Is there a way to do it from the right, so I can peel out State, then Suburb, then the rest.

 

eg:

 

 

17 CHURCH ST, PERTH, WA

3 PLAIN ST, EAST PERTH, WA

U1, 23 QUEENSBERRY ST, CARLTON, VIC

U1, 41-53 VICTORIA PDE, FITZROY, VIC

 

What I want from the list above is:

 

17 CHURCH ST PERTH WA

3 PLAIN ST EAST PERTH WA

U1, 23 QUEENSBERRY ST CARLTON VIC

U1, 41-53 VICTORIA PDE FITZROY VIC

Edited by Ex-Hasbeen

Share this post


Link to post
Share on other sites
Ex-Hasbeen    3,740

Under data menu, text to columns from memory. Use delimited rather than fixed width, using comma as the delimiter

I'd thought of that, but with some data having more commas than others, how do I get around that?

 

I need the text after the last comma in the 3rd column, the text between the 2nd last and last comma in the 2nd column, and everything before the 2nd last comma in the first column.

Share this post


Link to post
Share on other sites
Cottoneyes    737

To be honest, I would be looking for some sort of sorting before hand. Sort and do a different text to columns on those starting with U in your example. Manual I know but not sure of any other way so would use sort and other methods to reduce the amount of manual work required

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×