Excel Sorcerers!

[thesameguy]

I have run out of talent in Excel.

I have one column that has multiple elements separated by a slash, eg one/three/five/seven and I need to break that single column into multiple columns BUT I need that breakup to happen in a specific way - one needs to go into column 1, three into column 3, etc. Like:



Original
A
B
C
D
E
F
G
H


1/3/5/7
1

3

5

7



2/4/6/8

2

4

6

8



Any ideas?

[Kchrpm]

Hmm...
What are the elements? How do you know what columns they need to go in to?

[Kchrpm]

The solution I have so far is sloppy, inflexible, I don't like it, but it works, technically.

Why not text to columns? Makes it a lot easier once they're already separated.

[Kchrpm]

Ok, came up with a better solution, using formulas to change each of the symbols into different characters so the data between them can be easily extracted.

[Kchrpm]

https://docs.google.com/spreadsheets/d/1WjGB4U3iTKLUNpthAnf4ZPvODm40Fh0ybVrOL0cM1M8/edit?usp=sharing

[wang]

http://24.media.tumblr.com/tumblr_lj1drpAofD1qhb9t2o1_r2_500.gif

[wang]

tsg: are there any other possible combinations in your first column? like 2/5/3/6? and can there be cells with different numbers of digits?

OR DID KEITH RECOVER THE SIX DEMON BAG AND YOU'RE ALL SET?

[thesameguy]

There are ten possible original values, and each of those ten possible values go into one of ten specific columns... value 1 is always column 1, value 2 is always 2, etc. I think that sorcery works perfectly. I was looking for a way to move values from original to destination, but I see how sucking values works better. I'll try that tomorrow! Thanks!

[thesameguy]

Minor tweaks, but that totally worked. Thanks!!

[Kchrpm]

:up:

[George]

Oh mighty Excel Sorcerers and Corvette lovers everywhere, wons't thou please favor us with a wicked cool "=if" formula that I used to know how to write but have forgotten?

I have a column of values. They're all numbers currently, but I used to use this formula for words too. I will sort the column so that any duplicate values will be listed sequentially, like this:

1
2
3
3
3
3
4
5
6

I'm looking for this:

If A2 = A1, write "duplicate" in B1. If A2 does not = A1, write "not duplicate".

Then I would paste the formula down column B to get this desired result:

1 not duplicate
2 not duplicate
3 duplicate
3 duplicate
3 duplicate
3 not duplicate
4 not duplicate
5 not duplicate
6 not duplicate

My everlasting gratitude awaits your formulaic wizardry.

[thesameguy]

How does the formula know how far down Column A to look? Like, would it ever go:

1
2
3
4
1
1
1

and then have to "put duplicate" in B1 because 1 appears again in 5-7?

[George]

Nope. By starting it as =if A2 = A1 (that may not be the correct code, but you get the idea), you're asking "is the cell right below this one a duplicate or not?"

As you paste/drag the formula down, it automatically changes as it goes down, like this:

A2 = A1?
A3 = A2?
A4 = A3?
A5 = A4?
...and so on.

That's why I sort the list first, so your list becomes this:

1
1
1
1
2
3
4

There is a handy tool in the Data menu in Excel 2010 (and I think 2007 too, but not 2003) called Remove Duplicates that works without sorting, so in this case...


Like, would it ever go:

1
2
3
4
1
1
1

and then have to "put duplicate" in B1 because 1 appears again in 5-7?

...the Remove Duplicates command would recognize and remove three of the ones in that list without sorting. I will also give you a report when finished: "67 duplicates removed; 147 unique values remain", or something like that.

However, I'm not trying to remove duplicates. I just want to identify them.

[George]

Aha! I figured it out - too easy using the Formula Wizard. I was trying to type the formula in from memory.

It's shown here for Column M, which is where I started. I have a header row, so M2 was the first cell to check.

=IF(M3=M2,"Duplicate","Not Duplicate")

You can M3=M2 to whatever you want - A2=A1, or wherever you're starting.

Also, you can change the words in quotation marks to whatever you like.

=IF(M3=M2,"Corvettes are just re-badged Malibus","Corvettes are unbelievably badass")

Oh well...if nothing else, I now know where to find this formula the next time I forget.

[thesameguy]

That's why I wondered... it's only a problem if the potentially duplicate numbers are spread out... if you can sort by that column, it becomes much easier! :)

[Kchrpm]

You can do it without sorting.

=if(countif(A$2:A2,A2)=1,"Not Duplicate","Duplicate")

It counts how many times the value in A2 shows up in any cell between the top of the list (A$2) and the current entry (A2).

[thesameguy]

Nice!

Is there a similar way to make it count the entire column A, or even a wide range of column A?

[Kchrpm]

Yeah, just use A:A instead of A$2:A2

That won't find duplicates, though, it will only find items that are duplicated. If something shows up 5 times, they will all return 5. The original formula I wrote will result 1, 2, 3, 4 then 5.

[George]

You can do it without sorting.

=if(countif(A$2:A2,A2)=1,"Not Duplicate","Duplicate")

It counts how many times the value in A2 shows up in any cell between the top of the list (A$2) and the current entry (A2).

Great stuff. Thanks K!

I saved that on my new cheat sheet. I used to have one at my last job but never bothered to put one together here, until now.

The neat thing about Excel is there are lots of ways of getting the same results. I thought I was pretty cool by sorting a column and then labeling duplicates, but someone always knows a better/faster/more elegant trick.

[Kchrpm]

Someone used to have to do it with thousands of new entries every day :)

[Kchrpm]

You guys want to play with something for me?

https://docs.google.com/spreadsheets/d/1MWL2jNaH4N08AYqFN_Z1_5YSatsagpnFnsJSFMqhark/edit?usp=sharing

Edit the green cells, leave the black ones alone (they don't have protected ranges set up yet in the new version of Spreadsheets, so I have to just use the honor system).

[Kchrpm]

It's not perfect for situations where a large minimum down payment is required, I'll need to think on that.

[George]

Wish I could help with that here on a Friday at work but I can't get to your link. I'll give it a look from home tonight (maybe) or this weekend (more likely). Worst case - I'll download it from home and bring it to work on a flash drive and do it Monday.

[Kchrpm]

It's a Google Drive file, are you using an old Internet Explorer? I don't know how it would take to being downloaded. It's nothing that complex, just kind of wanted a sanity check to make sure I'm not missing anything glaring.

[George]

I'm at work with Internet Explorer. I don't know the version but I have Windows 7 and MS Office 2010.

When I click on your link, I get:


Internet Explorer cannot display the webpage
What you can try:
Diagnose Connection Problems
more information:
More information

This problem can be caused by a variety of issues, including:

•Internet connectivity has been lost.
•The website is temporarily unavailable.
•The Domain Name Server (DNS) is not reachable.
•The Domain Name Server (DNS) does not have a listing for the website's domain.
•There might be a typing error in the address.
•If this is an HTTPS (secure) address, click Tools, click Internet Options, click Advanced, and check to be sure the SSL and TLS protocols are enabled under the security section.

I've seen this before when a site was blocked at work but I could get to it from home.

Edited to add: I just selected all the SSL and TLS protocols as suggested but no dice.

[FaultyMario]

I have multiple choice answers in two rows where the first column is the question number plus the option letter, [7a,7b,7c, et cetera] and the second column assigns the value "1" only to the cell that was chosen for example if for question 7, someone chose e, the number "1" appears on the cell to the right of "7E".

Now, I need to condense it so that I only have cell "7" and the cell to its right should only read the letter that the value was assigned to "7"+"E".

Is that a "CountIF"?

[Kchrpm]

So you have

1
1a
1b
1c 1
1d

And want

1 c

Correct?

[Kchrpm]

I would make a second and third column (C & D) that checks if the second (B) has a 1. If it does, then D would be the rightmost character of the first column (A), and C would be the value of A with D removed (value so that it will sort correctly later, not as text).

Then you can just have a numbered list column wherever you want and do a vlookup for the corresponding answers.

[FaultyMario]

I resorted to a matrix (of the choices by the number of answers), I first pulled the value on a new row (I did and "IF" for the corresponding column), and then substituted "False" for blank, and after that I concatenated. I want to know how to do it in one step.

[Kchrpm]

It's difficult (perhaps impossible without macros) to do in one row because you end up skipping a varying number of cells.

[FaultyMario]

Do you want to see me thrown out of the conjugal home?
If not, please help me get the woman's contacts back to her Moto G.

There's a phone-to-web service her carrier offered for a couple of bucks when she got her new LTE sim card. She got on board and nice and round her contacts are on the website. When the "restore to sim" function is clicked, internet magic starts but then nothing happens. Woman goes mad. Foam around the mouth and all that.

Now I've got those contacts from the website onto an Excel Spreadsheet, they're one column and it's just name and phone number. Each contact captured in a single cell.

What do I need to do to get them all into an Android contacts archive? I could split them and make multiple columns or trans-paste the information onto a template if needed.

[Kchrpm]

Easiest thing to do would be a text to columns with space delimiter, then cleanup from there, and then move into the Google Contacts import template ( https://support.google.com/mail/answer/14024?hl=en ). Then you get all those contacts and numbers synced onto Google, and you never have to worry about transferring them again.

It's hilarious that carriers still offer that, that feature has been built into every smartphone for probably a decade now, but people don't know about it or bother doing the minimal setup it takes, so they pay extra and/or have to tell everyone to text them every time they get a new phone.

[George]

Mr. Kchrpm - and any other interested challengers - I have quite possibly the most difficult Excel challenge EVAR!

Fortunately, I don't NEED to be able to do this, but it would make my life a little easier at work.

Invoices have due dates. Our company issues payments every Thursday and pays invoices with dates to pay through the following Friday. So, this week on Thursday April 9, we will pay invoices with due dates up to and including Friday April 17.

Challenge:

Given a spreadsheet of dozens or hundreds or maybe even thousands of invoices with due dates formatted as dates in one column, is it possible to add another column with some kind of formula to show the projected actual date of payment?

Example:



Due date Projected payment date
4/7/2015 Thurs 4/9/2015
4/9/2015 Thurs 4/9/2015
4/11/2015 Thurs 4/16/2015
4/16/2015 Thurs 4/16/2015
4/18/2015 Thurs 4/23/2015

[Kchrpm]

If the due dates start in A2

=A2+choose(weekday(A2,1),4,3,2,1,0,-1,5)

[George]

HEY, THAT ACTUALLY WORKS!

Wow. Thank you, sir! This will make me THE MAN at work.

I had never heard of a "choose" function in Excel.

Mucho appreciado! :up:

[Kchrpm]

Choose is cool, it lets you do any number of different functions, kind of like unpacking a bunch of nested if/then statements.

[George]

That makes sense. I thought the formula would be something like this one that I use to show aging buckets.

=IF(T2<=30,"1-30 DAYS",IF(T2<=60,"31-60 DAYS",IF(T2<=90,"61-90 DAYS",IF(T2<=99999,"91 DAYS OR GREATER"))))

[Kchrpm]

Yep, but since the formula to find what day of the week a date is spits out a number, usually between 1 and 7, the easiest thing to do with that is use Choose.

[thesameguy]

I just spent a half hour learning about making Excel do bell curve charts.

AIN'T NOBODY GOT TIME FOR THAT!

[Kchrpm]

Some of the fun days at work are when they ask me to figure out how to make a chart in Excel that previously they've only drawn free-hand in Powerpoint or on a whiteboard.

[George]

1. Take Polaroid of whiteboard
2. Scan it and save to desktop
3. Insert picture in blank spreadsheet
4. Crack open beer, prop feet on desk, and return to web-surfing.

[Kchrpm]

You presume that they actually have numbers on the example they're giving me :)

[FaultyMario]

Just finished doing the monthly bills account.

I suppose teh sorcery has its limits...

[George]

Mighty Excel Champions, please lend me your ears.

I have:

An Excel workbook with between 400 and 500 worksheets. Each worksheet is just one page if printed.

"Cute PDF Writer" printer driver - this lets me save a document as a PDF file

A folder on my desktop

What I know I can do:

Select any worksheet and print/save it as a PDF file into the folder on my desktop

Select any number of worksheets, including all of them, and print/save them as one multi-page PDF file

What I would like to do, but don't know how:

Select all worksheets and have the computer to save them as 400+ individual PDF files - one PDF file per worksheet.

I realize I can select each worksheet one at a time and save them as individual PDFs, but I don't want to have to do that over 400 times.

I have looked around all my print options but can't see an easy way to do this. I am also familiar with creating macros in Excel, but don't know if a macro is what I need here.

Thanks for any help or wild-ass guesses!

[Kchrpm]

http://www.mrexcel.com/forum/excel-questions/646486-printing-multiple-worksheets-seperate-pdf-files.html

Or apparently you can open up a PDF in Acrobat and then use Extract Pages to separate them out.

[George]

http://www.mrexcel.com/forum/excel-questions/646486-printing-multiple-worksheets-seperate-pdf-files.html

The code in post #2 of that thread works perfectly. All I had to do was change the generic saving path of "C:\Users\JohnDoe\Documents\" to my own.

THANK YOU KCHRPM! Super-solid advice, as always.


Or apparently you can open up a PDF in Acrobat and then use Extract Pages to separate them out.

Probably so in Acrobat Pro or whatever the top-of-the-line version is called, but apparently I have only Acrobat Reader and I don't see Extract Pages as an option in any of the menus.

Metrics to add to your Excel advice-giving resume:

After I posted my question here, I told my manager I posted a question on the internet, but for now I didn't know how to do this. She assigned someone to go in and manually break out 100 of the 450-ish tabs. That took one hour, according to the unfortunate employee who had to suffer through that process.

I was able to press the Run button and watch the computer save 100 PDFs in perhaps ten or fifteen minutes. I went and fixed my lunch while it was running, so I don't know the exact time.

By doing the next 350 or so in the same way, we should save 3.5 hours of labor. :up:

[Kchrpm]

Good :up:

[George]

Personal boast:

I may have mentioned before that everyone in my department is losing his job because we are being outsourced to India. Well, a short time ago I was copied on an email containing a spreadsheet that needed nine columns of details added to each line, such as date, amount, identifying number, person who "owns" the item, current status, etc.

The email I was copied on was to the new department manager in India, who I met, trained, and was impressed by as a knowledgeable professional.

His reply to the email requesting the status of everything on the tiny little 142-line spreadsheet was "Too hard", followed by a frowny face smilie in Outlook. He said he would see if he could find someone to help.

26 minutes later, I sent the finished spreadsheet to the person who sent it to India - to that person only, who is someone I trust not to mention my name. It was nicely formatted and looking good, too. :up:

I mentioned I'd be interested to see what India sends back, and when, if they ever do. I think she'll probably forward it to me so I can compare my skills with theirs. Who knows - I may be humbled, but I don't think so.

Okay, that's the end of shameless bragging (for now).

Question for the Erudite Expert of Excel, that Master of Macros, the Viceroy of Vlookups, and the Fearless Foreman of Function Formulas: is it possible to vlookup cell colors, as well as content? I have a couple spreadsheets to combine, and one of them has rows that are color-coded, as well as categorized.

If not, I'll just filter on each color and add the necessary content to the category cell. Example: if Green = Completed, I'll can add the text "Completed" after the current contents of each cell, with a formula such as =A1&" Completed", or maybe better yet, I could just add a column for Status.

However, I'm just curious and always looking to learn more about this awesome video game...er...boring spreadsheet program. Thanks as always.

[Kchrpm]

Nope, can't use VLOOKUP to directly lookup cell formatting. I usually do a setup where, rather than changing a color for a status directly, I set up conditional formatting so that the color changes automatically when I write in Completed/Yes/No/whatever. Depending on the goal, I'll even change the color of the text to match the background so it's not visible, and if I'm doing that I might just use numbers to represent the various states.

[George]

I've probably worn out my welcome in here, but if not, here's one that is making my brain hurt today.

I'm doing vlookups.

In the worksheet where my "answers" are, I have columns A through H.

A is the number I'm looking up, from the spreadsheet where my "questions" are, and then columns B through H contain the "answers" I want to move over my my "questions" spreadsheet.

So, my formula looks like this in the first row, and then I've copied this down through the last row.

Of course these go horizontally in Excel instead of vertically, as I had to list them here.

=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,2,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,3,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,4,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,5,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,6,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,7,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,8,FALSE)

My problem is I have five values (and no blanks) in Column H, which is "Column 8" in the formula, but only one are showing up after the vlookup formulas have run.

I've manually compared to make sure what I saw at first glance is true: data in the columns "2" through "7" seem to be correct, but Column "8" (Column H) is only showing the one value for everything. I've retyped the formulas many times but still can't get around this error.

Here's a crude example with only two columns of vlookup results:

Let's say this is my list of "answers", and I'm trying to move the model name and color over to match a spreadsheet of "questions" that only has One, Two, Three, etc. listed vertically in Column A.

Correct information:

One - Camaro - Blue
Two - Corvette - Green
Three - Corvair - Red
Four - Chevette - Brown

VLOOKUP results:

One - Camaro - Blue
Two - Corvette - Blue
Three - Corvair - Blue
Four - Chevette - Blue

I'd love any suggestions, including to quit and go home.

Oy, I have a headache!

[Kchrpm]

1) File names with periods in them scare me
2) Your shit's broken.
3) Without looking at the actual file, it'd be hard to figure out what the quirk is in the data/settings/formulas that could be causing it. Everything looks right with what you've presented, but usually there's some weird coincidence that is left out when trying to translate. The fact you miss it during translation is the same reason you miss it while troubleshooting.

[George]

Fair enough. Thanks for taking a look. :up:

Part of the problem is I'm combining six spreadsheets into one, and who knows what each person did to his spreadsheet in terms of formatting changes and whatnot.

[George]

I finally figured it out.

One person in another location was a running large report and saving it in Excel. This was the report I used to run, but they wanted someone else to learn how since I'm getting the boot soon.

A second person (also learning how - go team!) was receiving the full report in Excel, and, as I was told, SPLITTING UP the report (more on that later) and sending individual spreadsheets to other people to add additional information.

Each person would add their data and then send HIS OR HER INDIVIDUAL SPREADSHEET (more on that later, too) back to Person 2 for review, questions, etc.

Once all that was complete through daily work, at the end of the week, Person 2 would send it to the only person left in the department *ahem* who knows how to do a vlookup to combine them and produce a finished spreadsheet for the week.

Sounds like too many cooks in the kitchen, right? Damn straight! Poor communication was the problem here, as it so often is.

I thought I had solved a lot of problems a couple weeks ago after explaining to everyone that they may not delete columns, insert columns with notes just anywhere, or otherwise customize their individual spreadsheets to the point that I had to combine everything manually, instead of letting Excel do what it can do so quickly and easily, if everyone will just put their notes in the columns provided and in the order provided.

Simple, right?

Well, no.

As it turns out, Person 2 was not SPLITTING UP the report at all. And, each person was not working on HIS OR HER INDIVIDUAL SPREADSHEET at all. Oh no. That would have made too much sense.

Person 2 was color coding rows of the spreadsheet for each person to work on. Remember my earlier question about vlookups and color formatting? Well, she was color-coding certain rows, based on date ranges, and sending the exact same spreadsheet to everyone and saying, "Joe - you work the blue rows. Shirley - you work the yellow ones", and so forth. Then, unbeknownst to me, she asked everyone to remove the colors from the spreadsheets before returning them, "so as not to mess up the combining process", or something like that, after I merely mentioned that I was not able to keep the colors intact through the vlookup process. I didn't say there couldn't be colors, just that that information would be lost on the combined report. Had I seen all the different colors, I might have understood what has happening before now.

Well, this is kind of funny, now that I have a clue. My problem today is that I thought each person had a unique spreadsheet, so there could be no possibility of duplicate information. That wasn't the case, and that's why all the cars on my report were blue, in my earlier example.

Additionally, there are date ranges on the report. What made things worse, to continue with my earlier example of cars, is that one person was working on '67 - '69 Camaros, another with '70 - '81 Camaros, and so forth. What a mess! :lol:

At least I've learned something today. Thanks again for the help, Keith. You've made me look good at work more times than I can adequately thank you for.

[George]

What is up, sheetheads? :lol:


https://youtu.be/xubbVvKbUfY

[George]

Anyone still giving out free Excel advice here? I sure could use some, if so.

I am sent a monthly PDF file originally created in Excel with perhaps fifty to seventy lines of text that I have to key in to other software. The dates are all in 2020.

This is a "saved as a PDF" file from Excel rather than a scan of a printout, so I can highlight and copy the text of the PDF and paste it into Excel.

However, when I do this, all the dates change from 2020 to 2016. I've tried pasting as values and everything else I can think of, such as making sure the cells are formatted as Short Dates, but the dates always paste in as 2016 no matter what.

Since the person who is creating these spreadsheets is using Excel, I requested they send me the Excel version along with the "official PDF file" just to help me enter their data faster (it's faster if I can sort the data the way I want it, rather than how they send it to me). And they did. Cool!

But, their spreadsheet now shows 2016 on all rows also. :sadbanana:

Am I (or we) missing something here? How can I get 2020 dates from a PDF to paste into Excel without the year changing?

Also, I know if I cell says 1/1/2020, I can enter a 4 in the next cell and then do =A1+B1 and get 1/5/2020 as a result. But I'm thinking doing that over four years, with two of them being leap years, would be a mess.

Thanks for any ideas.

[Tom Servo]

I want to start by saying I am not an Excel expert. I once made a formula and it worked most of the time.

Have you tried pasting into another app, like Notepad? I'm wondering if there's some weirdness going on with the PDF rather than Excel.

[Kchrpm]

That is a weird problem. Here's a formula to convert all the dates to 2020, using A2 as the cell where your date is:

=DATE(2020,MONTH(A2),DAY(A2))

It takes the month and day from A2, but uses 2020 as the year.

No idea what's causing the issue, but that's at least an easy way to get the right numbers out.

[George]

I want to start by saying I am not an Excel expert.

Who let this guy in here?

:P


Have you tried pasting into another app, like Notepad? I'm wondering if there's some weirdness going on with the PDF rather than Excel.

Yes, I did. I've tried a blank email, Word, and I guess it's Notepad that comes up when you right-click on the desktop and select New Text Document. I was trying to get the date column to lose any connections it might have had to the spreadsheet, such as some formula from 27 versions ago that's still there, even though I can't see it.

Thanks to Kchrpm for the formula. Looks like I won't need it now, as the sender provided a corrected version. Her comments: "Whoa, that was odd." Yes, indeed.

Much appreciated, gents.

[Rare White Ape]

I have a question!

See how column D in the screenshot is nice and narrow? I think it looks great.

But look at E20 with the '100' in there kinda not filling the cell very nicely. I want to make that cell narrower, but only that cell, without affecting the cells above or below, just so that I can place the result in a nicer spot.

No practical reason, I just want to make it look nicer.

Yes I am using Google Sheets. Die mad about it.

https://i.imgur.com/X8KUHOz.png

[FaultyMario]

Spreadsheets are function over form. Deal with it. :cool:

[dodint]

The fact that you left-justified only one number cell on the sheet tells me you're not taking this seriously. ;)

[Rare White Ape]

We she looks very different now. Bit of a hack job to get those 100s looking nicer.

Also I am open to suggestions re left vs right justification. I've even got some text... in the middle!

https://i.imgur.com/Rc0At8f.png

[Kchrpm]

Google Sheets is great.

Unnecessary yellow background not so much.

But, according to A38, you're a bit of a "Concentratrion" anyway.

[dodint]

I like Google Sheets. I use it far more than I use Excel anymore.

[Rare White Ape]

Keith, nice pickup on the typo.

The yellow is staying.

[George]

I think that's more of a flax color than yellow.

But, it is nice to see some activity in this thread again. Look how many people are just waiting to talk about something - anything! - other than the same handful of topics at the top of the Pit Stop every day.

Carry on, sheetheads.

[Dicknose]

Im a fan of right justifying numbers, especially if they have the same number of decimal places.
Then bigger numbers extend left, which to me looks more natural.

Hmm did I see that you shrunk the column width, then combined cells where you needed wider?

Sheets aren't really designed for having different sets of data. Yeah you can make it work, but that wasn't how they were originally designed.

[Tom Servo]

Waiting for one of you to compile C into Excel: https://mrthefakeperson.github.io/Excel-Virtual-Machine/

[Kchrpm]

If I knew how to write in C, I still wouldn't do that.

I have, however, transitioned to using Google's version of free database and visualization tools instead of spreadsheets when possible. That's how I do the fantasy F1 stuff (though there's a spreadsheet intermediary for specific things I couldn't figure out how to do), and how I track most of my trivia hosting stats (but not all, because again there are things I couldn't quite figure out how to do).

[samoht]

Edit - wrote the below without realising there was a second page to the thread (!) Doh. However, my general advice still applies, don't multiply/divide by 100 for percentages, just keep the values as they are and apply a percentage format.


If you made one cell in a column narrower without changing the others, then subsequent cells would be out of line with their column. I think it would quickly become unmanageable. Try drawing out what you want on paper and I think you'll see.

In this case, I'd argue that the "x 100" is not needed.

Google Sheets has a 'format as per cent' button in the top bar. So what I would do:
- make E20 the 'answer cell'
- set the formula for this answer cell to be simply 'B20 / C20' ie solute over solution. This will give 0.4375.
- press the '%' button in the toolbar (or go format->number->percent in the menus) This will give 43.75% as the answer
- then change F20 to just 'w/w'.
- change D20 to equals, as in the above examples.


I.e. maintain the underlying value as the decimal value 0.4375, and use formatting to display that value as a percentage, rather than actually multiplying the value.
The benefit of this approach is that you can then use the calculated percentage value in further formulas, without having to divide it by 100 again - it avoids confusion.

[Dicknose]

Good point!! Using formatting rather than modification.

Using modifying if you need to operate on the data, eg round numbers, then sum. If you just want to display rounded, use formatting. But rounding before summing is different to summing then rounding.

[Rare White Ape]

Yeah thanks for the suggestions. Unfortunately I can’t do much rounding because I’m not sure what level of precision I need just yet. It depends on the task I guess.

Plus I’ll need to rework some parts of it because I need the inverse equations too, and the molarity section is completely backwards and not user-friendly!

[samoht]

Thought I'd quickly knock up what I was thinking:

https://docs.google.com/spreadsheets/d/1kGdfBDwNBhi1M41dz35ETI2XpdIBlBGTyWGIRkcUN1o/edit#gid=0

https://lh3.googleusercontent.com/pw/AM-JKLXOaXC8NwAU5HYL5W2YHdnxuMVcbwknUk1qmJ1dWXGui-rigqCIInMy8BK5s6zZRq3mXpogSwT5c4fNaoiR0wP48VDijune zItQiVs-p4sZcOr85GPSAhs_2OQ2UlShqjt6w9YdWjEyoH9AQjDgqarbKA =w1042-h969-no

The formulas for the green cells are just either B / C or B x C, as appropriate, and then formatted as number or percentage as needed.

[Rare White Ape]

Hey Excel people.

Today's question:

I want to calculate interest on a loan that I'm paying off. Can I do something elegantly and easily that:

1. Calculates interest daily
2. Allows me to input my repayments weekly or monthly
3. Spits out a total interest payment
4. Allows me to compare between different repayment scenarios?

I have used the instructions highlighted HERE (https://support.microsoft.com/en-us/office/using-excel-formulas-to-figure-out-payments-and-savings-11cb708f-c137-4ef8-bcf3-5137aaeb4b20#:~:text=Find%20out%20how%20long%20it% 20will%20take%20to%20pay%20off%20a%20personal%20lo an) and with some adjustment can change between weekly and monthly payments, but I don't think it calculates interest daily, and won't give me an interest amount result.

I have also built a really cumbersome table where on each row I take the previous month's balance, then add 30 days of interest, then subtract a monthly payment... repeat. I then manually find where it reaches zero and then add up the interest column.

Any ideas would be splendid, thank you.

[Dicknose]

Calculating interest daily is no different to calculating it weekly/monthly other than its effectively a higher rate ie compounding more frequently is same equation just different numbers.

As for weekly vs monthly - you do need to check if the repayments are equivalent.
Bank typically highlight that fortnightly repayments pay off the loan quicker than monthly. But this is mostly because you are paying more! They assume 2 week payment is half the monthly, but then there is 26 payments per year vs 12. Not surprising 26*.5=13 is bigger than 12. So you mostly pay it off quicker due to paying more. The "compounding" difference is tiny.

None of this is tricky Excel, its just getting the correct formulae for compound interest with repayments.
If you are just having a set repayment then its a single equation.
The only reason to do the "each row is a payment" is if you wanted to vary the repayments up/down as you go.

[Rare White Ape]

All understood.

However (at the risk of getting into the weeds of loan repayments a little bit) when the bank calculates interest daily, it's calculated based the balance of the loan each day but then charged once a month. So it might add $2/day but you don't see a charge until the end of the cycle when they plop $60 on it.

On top of this you might make a repayment into the loan account once a month, so it's continually calculating interest on the same balance over that 30 days, whereas if you pay once a week the interest is calculated based on the reduced balance thanks you you reducing it by a weekly payment rather than monthly. Assuming you pay the same amount over time between weekly or monthly payments, you actually do pay it off slightly faster due to the daily interest calculation, when ignoring the occasional 5-week month.

I suppose I probably could use the example formula in the link I provided and just multiply my monthly or weekly payments, and subtract one from another. In fact, testing it out it seems like a good way to go, as my cumbersome table has an error in that I calc'd everything on a 30-day month, and not all months have 30 days.

[samoht]

It sounds like you've got it worked out, use NPER to calculate the number of payments as a function of the loan total, regular payment amount and number of payments per year (12 or 52 respectively). Then you know how much you're paying and can work the rest out from there.


On the daily interest calculation, it sounds like the important things are that the amount on which interest is charged will change immediately after each payment, and monthly based on interest.

The one thing the built-in formulas probably won't support is interest charges applied monthly (if that's what you're saying), but payments applied weekly. I suspect in this case the formula will just use a weekly cycle for everything, so the calculation will end up being fractionally more expensive than reality as it'll model you paying interest on the interest, so to speak. I also suspect this will make only a negligible difference.

[Rare White Ape]

Yeah difference is only a few bucks. But at the end of the day I have worked out that a $500 investment to fully pay off a credit card debt now will allow me to accelerate loan repayments, get it paid off in 14 months, and save myself about $340.

I just had a few scenarios in my head and wanted to work out which one was the best. Second-best will save me $310. It was close, so that's why I wanted to do some numbers :)

[Dicknose]

While they might charge daily, their advertised rate should be the equivalent.

Now another way to look at this "repay daily, weekly, monthly, yearly"... paying more frequently is no better than making the other payment at the start and saving interest on the amount of that repayment.
eg if I repay say $200 a week, instead of $800 a month... at best its like you got that $800 in before the loan started. You are saving interest on $800. Which is negligible compared to the overall loan.

Its not really worth wasting time on frequency of repayment other than having it match your frequency of income!! And if monthly even have it due soon after you get paid. The banks dont like paperwork, but they will move the repayment date if you ask.

(sorry gone off Excel talk)

[FaultyMario]

paying more frequently is no better than

The gift that keeps on giving.

[Rare White Ape]

:lol:

If you look closely, it's everywhere :lol:

[Rare White Ape]

To address DN's reply, I think the core of the query I have was whether I could input payments at a different rate to the calculated interest. The answer is... it's cumbersome.