challenging formula

[jo_park]

I have been tryiing to get this to work for the last few hours, and need some assistance. i believe it is an IF AND statement, but as I have tried, I keep getting false when changes are made

=IF(AND($AK9>0,$K9<>"Van",($AN$3*$AK9)),($AK9*$AR$3))

i am also trying to expand on this to say if AK=0 or is blank, then multiply AL by either AN3 or AR3 depending on what is in column K (multiple text)

Please help

 

[mathchick]

I'm guessing your parentheses are in the wrong spot

=IF(AND($AK9>0,$K9<>"Van"),($AN$3*$AK9),($AK9*$AR$3))

 

[Jonmo1]

Welcome to the board..

The problem with the posted formula, is the missplaced ) that is intended to end the AND function..
Also, you don't need the () around the multiplication results..

=IF(AND($AK9>0,$K9<>"Van",($AN$3*$AK9)),($AK9*$AR$3))
Should probably be
=IF(AND($AK9>0,$K9<>"Van"),$AN$3*$AK9,$AK9*$AR$3)


To add the if AK=0 or blank, we'll need some more information.
If it's 0 or blank, then multiply AL by either AN3 or AR3..
You need to provide information on how to determine which cell to use.

 

[texasalynn]

i am also trying to expand on this to say if AK=0 or is blank, then multiply AL by either AN3 or AR3 depending on what is in column K (multiple text)

by either??? what determines that?

 

[shawnhet]

I would guess that your formula above should probably be written as

=IF(AND($AK9>0,$K9<>"Van"),$AN$3*$AK9,$AK9*$AR$3) This will multiply $AN$3*$AK9 if K9 is not equal to "Van" and AK9 is greater than 0, otherwise it will multiply $AK9*$AR$3.

However, I can't make sense of how you are trying to expand the expression.

Cheers,

 

[jo_park]

Hi. Thank you.

the revised formula provided is now providing the correct calculation, to add the additional information, here's what I'm looking at.

to get the additional info, i need to "add" somehow that if column AK=0 or blank, then take the original formula written but replace AK with AJ, does that make sense?

 

[Jonmo1]

Try

=IF(AND($AK9>0,$K9<>"Van"),$AN$3*$AK9,IF(OR($AK9={"",0}),$AJ9,$AK9)*$AR$3)

 

[jo_park]

jonmo1

i tried what you provided, and once value in AK removed to test the AJ portion, formula pulled back zero. I cut and paste exactly as you provided, any other theories? (will keep trying on my end also

 

[jo_park]

sorry should say, it pulled a value but not correct? based on calculation it's out by roughly $90