Excel Nested If Functions

Rcjackson66 

Two observations and two suggestions:

(1) Look at your cell H31: it reads as follows (unless you've fixed it in the meantime):

=IF(C27="Self Gen",0.1*F31,0.05*F31)

What you mean is for it to read =IF(C26="Self Gen",0.1*F31,0.05*F31)

So suggestion #1: look into absolute and relative references in Excel Help. Here's a screen grab of the first part of what you'll read:

The net result of what you'll learn is that you should be using a formula like this in Cell H31, and then it can be copied as is to all the other appropriate cells; the absolute reference to $C$26 will stay as is, and the others will change. =IF($C$2c="Self Gen",0.1*F31,0.05*F31)

(2) Second observation: by incorporating the values 0.1 and 0.05 directly in your formulas, you are doing what's referred to as "hard-coding" what is in fact a variable. And variables, by definition, are subject to change. And if/when they change you have to go back to every formula in which the variable has been hard-coded and make that change. Granted, given find and replace that can be done, but that's itself not the most reliable method, given the possibility that there are other 0.1 and 0.05 references that you might not what to change.

Anyway, second recommendation: look into using named ranges and create a small table that could look like this:

 You do this using "Insert....Name....Create, by the way

After which your formula in Cell H30 would read like this: =IF($C$26="Self Gen",SeGe*F30,CoLd*F30)

Or, to use the formula recommended by @Savia :

=MIN(E29:F29)*IF($C$26="Self Gen", SeGe, CoLd)

And then all you'd have to do to update your commission rates is change that small table, to have it cascade seamlessly through all formulas that use those variables.

Now, I did notice in going through your sheet (I guess this is a third observation) that in fact you have at least one other complete commission schedule, with certain items doubling the numbers we've been showing so far. The named range notion would still work, but it gets trickier if you want to use the same formula for all these calculations. So let me ask you, before I go any further, if you have any interest in going further. The main idea is to use "business tables" to serve as the storage spot for things like commission rates. This has the aforementioned advantage of allowing you to make a change in the table that automatically and seamlessly gets used throughout the workbook or spreadsheet. It also has the advantage of transparency and visibility--it's always clear what commission rates are being used; they're not "buried" inside formulas. (In this case, just to underscore what I just said, it was only because I was going through the formulas that I noticed the two difference schedules for different kinds of products/services.)

So if you'd like to pursue this idea of business tables more fully, let us know. I or someone else can help you do it.