Reader Question: Complex Nested If/And/IfNot Functions

Excel function tutorials

I received a question along with the following table:

The question is as follows:

“IF the Transaction is a ‘delete’, AND the Rate Category is selected, THEN match the delete rate from the corresponding Rate Category List into the Rate cell, IF the Transaction is an ‘add’, AND the Rate Category is selected, THEN match the add rate from the corresponding Rate Category List into the Rate cell.”

Again, it’s about trying to go step by step. the first case here is:

IF the Transaction is a ‘delete’, AND the Rate Category is selected, THEN match the delete rate from the corresponding Rate Category List into the Rate cell

So here, I’d need an “if and” condition that will check the value of the first column, look if cell 2 is empty and if both conditions are met, do a vlookup to find the correct rate. Simple? Here is what I came up with:

=IF(AND(A13=”Deletion”,B13<>“”),VLOOKUP(B13,RSUI_Deletes,2,FALSE),””)

The second part is:

IF the Transaction is an ‘add’, AND the Rate Category is selected, THEN match the add rate from the corresponding Rate Category List into the Rate cell.

Here I will use a similar function of course:

=IF(AND(A14=”Addition”,B14<>“”),VLOOKUP(B14,RSUI_Adds,2,FALSE),””)

I can then combine them together as follows:

=IF(AND(A13=”Deletion”,B13<>“”),VLOOKUP(B13,RSUI_Deletes,2,FALSE),IF(AND(A13=”Addition”,B13<>“”),VLOOKUP(B13,RSUI_Adds,2,FALSE),””))

One last change I will make is to add an “Unknown” if the first column is filled but not the second by changing the formula to:

=IF(AND(A13=”Deletion”,B13<>“”),VLOOKUP(B13,RSUI_Deletes,2,FALSE),IF(AND(A13=”Addition”,B13<>“”),VLOOKUP(B13,RSUI_Adds,2,FALSE),”Unknown”))

The end result is:

As you can see what looked very complex ended up being rather simple:)

Reader Question: Nested If Condition

Excel function tutorials

One of the more recent questions that I got on the blog was the following:

I need some help.
I want to use an if statement for the following:
Quantity (d1)
Pk Qty (E1)
Total (F1)
UOM (AA1)

Formala Help to be entered in F1:
If pk qty = 0, then enter the value for d1 in F1, If Pk quantity is>0, then return D1*E1 unless UOM =FT, then enter D1

Let’s try ot resolve this one step at a time

First off, because I’m visual, I’ll put this into a spreadsheet:

Then, step by step:

“If pk qty = 0, then enter the value for d1 in F1″

=IF(E2=0,D2,””)

For the second part:

If Pk quantity is>0, then return D1*E1 unless UOM =FT, then enter D1

=IF(AA2=”FT”,D2,D2*E2)

Then, I can simply enter the 2nd part into the first formula:

The end result?

=IF(E2=0,D2,IF(AA2=”FT”,D2,D2*E2))

Once again, the key is doing this one step at a time, it’s that easy:)

Transforming Addresses Into Label Formats With Excel

Excel macros, Excel spreadsheets (.xls)

Today, a reader sent me a list of 400 addresses that were all entered in a format like this one:

P.S: I am not publishing the actual file for privacy purposes and don’t feel like writing down 400 fake ones:)

Her hope was to transform the addresses into what you’d use on a label such as:

I thought this would be a good VBA practice. So what I wanted to do was use a “loop” function to go through each line and for each one, write down the label over 4 lines. How did I manage? Here is the code I used:

And the end result? Simple enough? You bet

Google Spreadsheets Moves Closer To Microsoft Excel Capabilities

Google Cloud Spreadsheets

Last week, after publishing my most recent post about Google spreadsheet, I received 5 emails within a few hours from readers that are increasingly using Google docs. I asked them why? Not surprisingly, they said it was mostly the ability to have multiple users at once as well as the free cost. For years, the main drawbacks for Google spreadsheets (when comparing with Excel) has been the lack of advanced functions, macros, etc.

Things Are Changing Quickly

Over the past few months, I’ve published posts about getting financial data into the spreadsheets through the Google finance function, one of many “custom” functions.

Then last week I discussed the use of scripts to build “vba-like” functions

Today, I wanted to discuss some of the changes in the new version of Google spreadsheets that is being rolled out:

-Offline working: This is GREAT. Yes, I love working online and will always do so when possible. But the ability to work on my docs when offline is a major benefit. Yes, you need to use Chrome as your browser but that is what I always use anyway.
-Improved Formula Editing: This makes it easier to work with functions in a similar way to what Excel offers
-New Functions: They added functions such as “Sumif”, “Countif”, “AverageIf”… ring any bells?
-Filter views: I will need to experiment with this but it does look promising

Many other things were improved/upgraded and added.

Clearly, Google Spreadsheets always was an alternative to Excel but as time goes by, it’s clearer that the spreadsheets are quickly catching up to MS Excel. It’s still lacking many different functions but the gap is shrinking.

If you are not yet using Google Docs, I’d love to hear why, and if you are, could you imagine moving away from Excel at some point? I’m far from that point but I would say that I spend as much time in Google spreadsheets as I do in Excel now…!

Do Google Docs Scripts = MS Excel VBA?

Excel macros, Google Cloud Spreadsheets

There are many benefits to using Google Docs instead of Microsoft Excel which I’ve certainly discussed on this blog but one big downside in my opinion is/was the lack of complexity of Google docs. I run several excel spreadsheets that use complex functions, macros, etc.

Google Docs Scripts Could Be The Way Forward?

Google does offer ways to add complexity through “scripts” which I had heard about but had never used. I then ended up looking for ways to get the latest price of “Bitcoins” and while there were ways to do it with a simple formula, I did see a script which looked interesting so I decided to give it a try. I’ll tell you how step by step.

Step #1-Create A new spreadsheet

Step #2-Name a cell (this specific script would add the Bitcoin value into a specific cell so I needed to name a cell:

Step #3-Create A Script

Step #4-Run The Script

Voila! You can see the value here:

This is certainly interesting and I’ll without a doubt be reading up on these and trying several more. I doubt I’ll get rid of Excel anytime soon but if I’m able to move some of my spreadsheets, I’d be thrilled.

One big challenge that I expect is that I’ll certainly have a lot more trouble finding info about Google Doc scripts than Excel VBA.

Have any of you tried Google Docs scripts? If so, I’d love to hear from you.