Formula Question

[Atalkez]

Hello!

So, I have a spreadsheet with several columns that contain various information. I am trying to work out a formula that will check if a flag is set in two separate columns, then depending on a range of values in another column, determine a calculation for a final result.

So, in normal coding, it would be something like this:
if(C3 == "N") && (F3 == "Y") {
if (D3 > 200)
calculate and post
else
calculate and post
}
if (C3 == "N") && (F3 == "N") {
if (D3 > 200)
calculate and post
else
calculate and post
}

To make it a bit more clear, this is a commission calculation spreadsheet. The flag options are N/U for New/Used, and Y/N for Yes/No in regards to if the deal was split (which makes me divide by 2 obviously). The calculation is different for New/Used, and the split determines if the calculation needs to be divided or not.

This is what I have right now:

=IF(MATCH(N, C3, 1)), =IF(MATCH(Y, F3, 1)), =IF((D3*0.25)<200,"$190"), =IF((D3*0.25)>200, SUM(D3*0.25)))

This doesn't work, however, so if anyone can help clear up my mistakes in regards to the calculation, that would be very helpful

Thanks!

 

[dreid1011]

How does it not work?

At first glance, have you tried "" around the N and Y in the Match formulas?

 

[Atalkez]

The error that comes up is:

There's a problem with this formula.
Not trying to type a formula?
When the first character is an equal or minus sign, Excel thinks it is a formula
To get around this type an apostrophe first

I've tried removing all but the first =
I've tried adding "" around N and Y
I've tried adding ' in front of all but the first =

Not sure what else to try!

 

[Scott Huish]

First off that's not normal Excel coding, that is not VBA syntax.

Why are you using MATCH rather than just checking for equality. Also, the = sign only goes before the first IF in the formula, not all of them. All formulas start with an = sign to tell Excel that it is a formula.

Is this what you are looking for?

=IF(AND(C3="N",F3="Y"),IF(D3*0.25<200,190,D3*0.25),"")

I'm assuming you are returning $ so format the cell this way. rather than returning a text string of $190.

You didn't say what your calculations were for C3="N" and F3="N" so it is not included in my formula.

 

[Atalkez]

Yeah this is the first time I've ventured into Excel coding like this, so I figured my syntax was likely wrong in some places.

Thanks for clearing that up regarding the equal signs. There is no calculation in C3 or F3, they are just a one letter column I was using basically as a variable to check against.

The formula you posted worked for me, now I'm going to go through and add some more parameters on the calculation side to make sure I've got it all working perfectly.

Thanks for the help!

 

[Atalkez]

Alright, so I've got it working on everything except one part of the calculation. Formula is:

=IF(AND(C7="N",F7="Y"),IF(D7<800,(190/2)),IF(AND(D7>800,D7<1000,D7*0.3),IF(D7>1000,D7*0.35),IF(AND(C7="N",F7="N"),IF(D7<800,(190)),IF(AND(D7>800,D7<1000,D7*0.3),IF(D7>1000,D7*0.35),IF(AND(C7="U",F7="N"),IF(D7<800,(190)),IF(AND(D7>800,D7<1000,D7*0.3),IF(D7>1000,D7*0.35),IF(AND(C7="U",F7="Y"),IF(D7<800,(190/2)),IF(AND(D7>800,D7<1000,D7*0.3),IF(D7>1000,D7*0.35),""))))))))

It is returning everything properly for all calculations under 1000. However, if the D column is > 1000 - it is returning FALSE instead of anything else.

Thoughts?

 

[Scott Huish]

That means you're missing a statement for FALSE in there somewhere. In fact you are missing them many places, each IF should have something for what to do if TRUE and FALSE.

 

[Scott Huish]

You're also testing too many things. If you combine the like items, I think this will do all that you asked for:

=IF(D7>800,D7*0.3,IF(D7>1000,D7*0.35,IF(F7="Y",95,190)))

 

[admiral100]

Hi Scott,

Nice, but I think you're missing one more criterion

=IF(AND(D7>800,D7<1000),D7*0.3,IF(D7>1000,D7*0.35,IF(F7="Y",95,190)))

 

[Scott Huish]

No, I just should've checked for the 1000 first:

=IF(D7>1000,D7*0.35,IF(D7>800,D7*0.3,IF(F7="Y",95,190)))