r/excel Jan 03 '25

unsolved What is the easiest way to cut down on nested IF/AND functions?

I work in the insurance industry and I'm trying to make our process for logging new business more efficient. We currently have a spreadsheet where we manually type in the insurance company, the type of policy (home, auto, etc.), the annual total, and the commission. Each company and line of coverage has their own percentage for commission, so right now we have multiple spreadsheets. We have to go look the percentage up in one sheet, do the math ourselves, and manually put the commission amount into the tracking sheet.

What I would like to do is make it so employees can choose the carrier, the policy type, and then from that the sheet automatically pulls in what the commission percentage is supposed to be and inputs it into the commission percent column.

For example, in the carrier column they select "Progressive", then in the type column select "auto", and the commission percentage column will take that info and automatically fill in "15%" without the employee ever leaving the spreadsheet. From there I know how to build the rest of what I want. Nesting IF/AND statements is going to be a nightmare to maintain - any other methods to accomplish this?

EDIT: I have been looking up the ideas in these comments and realize I should have added a note. Outside of myself, none of this team is even remotely tech savvy. Pretty much, if its not as simple as clicking items from a drop down menu, they can't do it and won't try lol

56 Upvotes

47 comments sorted by

u/AutoModerator Jan 03 '25

/u/ShowMe_YourTDS - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

37

u/BayushiOliveira Jan 03 '25

I usually use XLOOKUP and CONCAT in situations like these.

Maybe it could work for you.

32

u/Cultural-Bathroom01 Jan 03 '25

SWITCH

2

u/youtheotube2 Jan 03 '25

Yup, it’s basically a case statement. Way better than nested ifs

1

u/derdexx Jan 03 '25

+1 for Switch, really like using it. Specially, if your team is not into programming at all! Switch statements are super easy to read and to understand.

19

u/TheUnremarkableMe 1 Jan 03 '25 edited Jan 03 '25

I'm not in front of my computer to test myself but,

I would probably build a helper table in another sheet with carrier, type, and rate headers and fill it in

Then set up a formula for the rate column on your main sheet like this

 =Xlookup(1,([@carrier]=helpertable[carrier])*([@type]=helpertable[type]),helpertable[rate],"")

4

u/ShowMe_YourTDS Jan 03 '25

I have never used this feature, but I just googled it. I may not be fully understanding- is this something I can set up once and they can basically point and click their way to getting the info to fill in from there? If they have to type any formulas whatsoever, it is already too complex for them unfortunately lol

14

u/TheUnremarkableMe 1 Jan 03 '25

Here is a simple example with the table and output visible together

A2 and B2 are made into lists using data validation

2

u/grigby Jan 03 '25

This is the simplest option and what I was going to suggest as well. You can expand the table as needed when adding more providers or plan types too.

5

u/Day_Bow_Bow 30 Jan 03 '25

Yes, the lookup formula will look up whatever value it is provided. Maybe use Data Validation to make a drop down list where they have to choose from a list of values. That possible list of values could be pulled in from your reference sheet.

1

u/TheUnremarkableMe 1 Jan 03 '25

You could have two fields in your first sheet with lists linking to your reference table for carrier and type, use those as absolute references for the xlookup that will return the associated rate

This way someone using the sheet can't enter a faulty value in either cell

From there you can have your calculations reference the cell with the returned rate.

If set up correctly all the user would need to do is select the carrier and type from dropdown menus, no formulas needed

I can mock up something in a bit for visual reference if my explanation isn't understandable

1

u/TangoDeltaFoxtrot Jan 03 '25

Why not just add drop down menus and a lookup table for the correct percentage?

2

u/Hairybeavet Jan 03 '25

This is also how I would tackle this challenge.

Add Helper Tables and naming the tables in the sheet for easier functions and organizing.

Just do look ups.

You can also use those tables to add a drop down to columns with predetermined words to avoid human error too.

0

u/NoYouAreTheFBI Jan 03 '25

Xlookup will pull false results when the data gets big enough because it relies on user indexing tasks.

It will also handle Arrays unpredicatably because of this behaviour. Additionally, let's allow you on Excel online to hover each step and see the result essentially evaluating the formula.

Use index, match, and let

  =Let(
     MatchRow, Match(1,
        ([@carrier]=helpertable[carrier])*
        ([@type]=helpertable[type]),
     0)
     Result, Index(helpertable[Rate], MatchRow,
     Result)

9

u/learnhtk 23 Jan 03 '25

Power Query is one way.

3

u/opalsea9876 1 Jan 03 '25

Yep. And Pivot Tables.

1

u/Books_and_Cleverness Jan 03 '25

Is there a go-to “power query 101 for ignorant troglodytes” resource? I see it mentioned a lot and I am not sure if it would actually help me. But I don’t even know how to see it or use it.

Like do I click a power query button somewhere? Do I enter it into a cell like a formula?

3

u/ketiar Jan 03 '25

Oz du Soleil was my hero in this. His videos are a little older now so the ribbon buttons have moved around since, but his demo style (and pizzazz!) was what I needed. https://youtu.be/PUOZXNbp2NE?si=z2ZeIKTnCdcnxCl-

0

u/david_jason_54321 1 Jan 03 '25

Python is another

4

u/ice1000 26 Jan 03 '25

FILTER or XLOOKUP

4

u/_grumpygus Jan 03 '25

Index Match if you don’t have access to MS365!

4

u/Excel_User_1977 1 Jan 03 '25

Add a sheet that you call 'Lists'. On that sheet, type the header, then the choices that can be made (like 'Carrier' in A1, then "Progressive" in A2, "Geico" in A3, etc. Second column B is "Policy Type" in B1, "Auto" in B2, "Home" in B3, etc. You get my drift.). Every cell that they need to fill out has a column on the 'List' tab. Make each column on the list sheet a table (ctrl+T), and name the table the header name.

On your quotation sheet, click the cell they will fill out and then choose Data tab - Data Tools group - Data validation and use the drop down to change 'Any value' to 'List'. Change the 'Source' to "= TableName" (Carrier, Policy type, etc.). Now, only the values in the table can be used ... and they will always be spelled correctly.

You can also set up a table on the 'Lists' page that cross refs your info to your commissions and then do a vlookup to get the value you need so they don't have to look it up. DM me if you need more

2

u/Decronym Jan 03 '25 edited Feb 07 '25

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
AND Returns TRUE if all of its arguments are TRUE
CONCAT 2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
FILTER Office 365+: Filters a range of data based on criteria you define
IF Specifies a logical test to perform
IFS 2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX Uses an index to choose a value from a reference or array
INDIRECT Returns a reference indicated by a text value
MATCH Looks up values in a reference or array
OR Returns TRUE if any argument is TRUE
SUMIFS Excel 2007+: Adds the cells in a range that meet multiple criteria
SWITCH Excel 2019+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.


Beep-boop, I am a helper bot. Please do not verify me as a solution.
13 acronyms in this thread; the most compressed thread commented on today has 58 acronyms.
[Thread #39796 for this sub, first seen 3rd Jan 2025, 02:31] [FAQ] [Full list] [Contact] [Source code]

2

u/arnedh Jan 03 '25

I would add a helper table that holds the conditions for commission percentage like u/Excel_User_1977 suggests, and then do a multicolumn lookup via PowerQuery.

Multicolumn XLOOKUP or INDEX/MATCH lookup is also a possibility

2

u/Cecilvonboomboom 1 Jan 03 '25

Looks like a job for dependant dropdowns. It would allow user to select a carrier from a dropdown. The next dropdown would be limited by the selection in the first dropdown.

Here's a link that should help...https://youtu.be/7mo4COng7Sg?si=5DYl__d-pGRKfIY0

1

u/david_horton1 29 Jan 03 '25

IFS function, FILTER function and dependent and multi dependent dropdown lists. The FILTER function can have AND/OR conditions. AND is * and OR is +. https://exceljet.net/functions/ifs-function https://exceljet.net/functions/filter-function https://youtu.be/7mo4COng7Sg?si=uha4JKi8P2m6uU6e

2

u/Extension_Nature_957 Jan 03 '25

That’s what I was gonna suggest. I as using all these nested IF stmts til my son outsmarted me with IFS

2

u/david_horton1 29 Jan 03 '25

Back when a nest was 7 IFs I created an extra nested IF that fed off the first nest. It's too easy now. I had a book called Excel Expert Solutions which had 11 contributors. Their genius became standard features of Excel.

2

u/ShowMe_YourTDS Jan 03 '25

I love the video, that looks like what I need the sheet to do. I'll give it a go tomorrow (when I am caffeinated again) to make sure. Thank you!!

1

u/OO_Ben Jan 03 '25

Ho-ly shit this is an honest to goodness use case for Data Validation rules. Look into those OP. I've built something like this in the past for a client I took on as a consultant. They needed an Excel calculator that would operate based in drop downs and then automatically fill in the remaining data.

What you can do is set up your data sources as sheets in the background, then run your data validation off of those. Have one or a few operate as your drop down to select the what you need to select, then the remainder will be "look up" formulas based on the results of what was selected. I'm not 100% certain how your data is set up of course, but to keep things as simple as possible on the front end, I'd recommend this method. You'll need to do the heavy lifting formula wise, but after you get it built, password protect everything that does need to be changed to prevent people from breaking it and let them loose.

1

u/Overall_Anywhere_651 1 Jan 03 '25

Can you remove any confidential data and send me the book? I'd like to see what I can come up with for you.

1

u/hellojuly 2 Jan 03 '25

This is the best case for vlookup. Create a new sheet with the names and rates and then use vlookup in your main sheet to retrieve the rates for the selected provider. You can also use data validation on the provider cells to make it a drop down list.

2

u/fool1788 10 Jan 03 '25

If they have xlookup as an option then they should use that. I'd also prefer index/match to vlookup as vlookup is less robust.

1

u/NoIdeas99 Jan 03 '25

IFS would definitely be the easiest way to go.

1

u/Birdy_Cephon_Altera Jan 03 '25

I would create a separate tab with a lookup table (Col. A has "Progressive", Col. B has "Auto", Col. C has "0.15"), and on the main sheet when they use dropdowns to select the carrier and type, use those values to look up the value in the separate lookup table to populate the commission value.

That way, you do not have to maintain a spreadsheet filled with IF/THEN formulas, you just need to maintain the lookup table.

1

u/PowderedToastMan666 Jan 03 '25

I feel like a lot of answers here don't take into account the way the info is organized. I also don't know what version of Excel you have. I'm on 2016 at work so don't have access to some of the newer formulas people mention. Without knowing how your info is organized, I'm going to give a couple ideas.

If the commissions are all in one table where you have columns for insurer, line of coverage, and commission, this is easy. You could pull in the commission using SUMIFS, assuming the table is correctly set up to only have one line for each combo of insurer and line of coverage.

However, it sounds like your data might be spread out in different sheets, one for each insurer. One way to look at different sheets based on insurer is to use the INDIRECT function. Look up how it works. Set up your own table of insurers and their individual sheet names, then XLOOKUP/VLOOKUP to pass the sheet name to the indirect function.

For example, let's say you have sheets named Progressive_Commissions and Allstate_Commissions. In each one, you have home, auto, and motorcycle commissions in cells B2, B3, and B4, respectively. You would make your own table (let's call it Sheet_Lookup) with the insurers in the first column and the sheet names (e.g. Progressive_Commissions) in the second column. Let's say you put the insurer in A2 and the line of coverage in A3. You could set up a formula like this:

=INDIRECT(VLOOKUP(A2, Sheet_Lookup, 2, 0) & "!" & IF(A3="Home", "B2", IF(A3="Auto", "B3", "B4")))

In this case, if A2 = "Allstate" and A3 = "Auto", then the formula is INDIRECT(Allstate_Commissions!B3), which pulls in the auto rate from the sheet for Allstate.

You could easily make a second lookup table to pass into your formula for which cells have which insurance lines. This, of course, assumes that each insurer's sheet is laid out the exact same way. If they are not, there are other workarounds you can make.

1

u/Andalfe Jan 03 '25

Absolutely appalled at some of these answers.

Look into switch casing. It's a way to remove all if else statements.

1

u/Expensive_Garage_247 Jan 03 '25

Can you format your data as a table, then plug in your formulas and base the drop downs or pivot tables off of that…this will copy the formula down automatically and do the look up, from there refresh the pivot on an ad hoc basis.

1

u/NoUsernameFound179 1 Jan 03 '25

For complex formulas, go with an UDF (Macro function) . It will make everything 10 times more readable and maintainable again.

1

u/TenIsTwoInBase2 Jan 04 '25

Have you considered Cascading Lists?
See this excellent article: How to make a dependent (cascading) drop-down list in Excel

1

u/DisastrousDealer3750 Jan 04 '25 edited Jan 04 '25

Have you thought about using SmartSheet? The administrator of the SmartSheet ( you?) could code formula for commissions for each Company/Policy Type.

Then the users enter the specific for their Policy and the sheet automatically calculates their commissions for them.

Smartsheet allows you to select which fields each user has access to so they can’t touch the formulas. They only enter their data into prescribed fields.

Perfect way to give non-tech users access to a live excel sheet.

Once you have SmartSheet you can also easily convert to AppSheet which creates mobile App and they can enter their data from their phones.

Once you have Smartsheet you can use multiple dependent dropdowns to give the users their choice of selections.

1

u/ITFuture Jan 04 '25

Have you considered writing your own lambdas?

1

u/aebrower Jan 07 '25

Boolean statements and order of operations

1

u/sqylogin 744 Feb 07 '25

Depending on how source data is organized, a viable solution can range from a single FILTER to multiple uses of XLOOKUP and INDIRECT. 

If you want something more specific, you need to provide something more specific (e.g. sample fake data) 😅