so i have a spreadsheet that has the following information for commissions



column a9 i have to the total of the product, and what i want is to compare that number in a9 and if its between a12 and b12, the commission is 0%, if its between a13 and b13, the commission is c13, if the total is between a14 and b14 then the commision is c14 and so on

how do i dod a nested if to do this. i am the first to admit i am HORRIBLE at math so any help would be appreciated

pfft, i don't care about money.

Subject: excel formulas




When I get a spare minute I'll see if I can figure out what the logic is for a nested IF function in Excel, with cell references. I'm a bit rusty with excel formulas, but I hope I can help you figure it out. I have to go eat dinner first.



This is what I've got so far. I'm wondering if the OR function can be used too. In regular programming I would use a "switch" but I don't think that's a possibility with Excel. Would be easier tho. lol..

I guess you can nest up to 7 IF functions, so here goes.

I just tested this formula ' in cell D19 and it's logical so..



Now I'll try and nest all 6 commission rates for ya.

You realize peeps started this thread, right?

I guess you get more bees with honey.

I'm trying hard.




EDIT! I got it!! Just had some parenthesis in the wrong place.

This quite possibly is the nerdiest thread on RM.

Excel formulas are nerd lite, actually.

Yeah, a real nerd would write this out in c++

Px are you any good at Excel? I dread it but i've compiled an excel spreadsheet of all my pj gigs attended under the assumption I could pull up how many Given to Flys I've seen for example, yet I can't figure that out on excel at all. Any ideas how?

I haaaate excel.

Wasn't there a website that had a tool that would tell you that information?

it had pretty colours too if i remember correctly.

I love Excel. Love Access too. But I don't get a chance to utilize either of them much. At my last job I was using them, but I encountered a tall poppy situation.

I'm not certified (story of my life), Dime! But I would use Filters (Data tab) to pull up how many times you've seen Given to Fly.

nope those forumlas did not work

here is the layout of the actual sheet (formulas)



sans formulas



as you can see, the formulas that are showing the correct commission are hard coded in, but do not check to see if it can be any other commission rate, it assumes.

the purpose is to get a forumla to check to see where the data falls into the range of the commission chart

It helps to see the entire worksheet. You won't be able to copy and paste the formula I did, because the cell references are all skewed. But the function will work. Each cell in row 8 will require that same function but you'll need to adjust the cell parameters to call the data from the product produced in each respective cell in row 7.


I built a replica of your worksheet based on the formulas through row 6 so far. Question though: In row 7, shouldn't the results from row 6 all be multiplied by 10? You have them multiplied by 11 in cells B7 and C7.

I can adjust the formula for each cell in row 8.

brb.

Hey Peeps, you have the commission rate in cell H8 hard coded wrong. It should be 4% not 3%. No biggie tho.

I've got your formulas. Let me know if they work. Make sure you hit enter after pasting each one into the formula bar, or the cells will get wonky.

for cell B8:


for cell C8:


for cell D8:


for cell E8:


for cell F8:


for cell G8:


for cell H8:


for cell I8:



The main parameter that is being changed for each cell in row 8 is every instance of "IF(AND(B7>" B7 becomes C7, then D7, E7 etc

nope, didnt work, and im really starting to hate excel

hmm..




Here's a zip file to download with the worksheet I mocked up. Maybe just compare the syntax.
http://www.centrxsystems.com/pxzilla/peeps_function.zip

I'll break down the function in Gimp today so you have a visual aid as to how it's structured.

i actually got some help elsewhere, excelforum.com

and there was really no need for a forumla


=B7*LOOKUP(B7,$A$14:$C19)

basically it looks at the first number in the column and sees if its in its range without going over, then does the multiplication itself. so if the formula is in b7, it will then multiply b7*lookup(b7, compares to $a$14 to $c$19 the range of criteria: $c19 what it multiplies it against if found )

thanks for trying

Oh yeah! A lookup table is the other way to go. Sorry, you mentioned a nested formula so I didn't even consider the alternative.

Glad you got it worked out.