# challenging formula

#### jo_park

##### New Member
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)

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I'm guessing your parentheses are in the wrong spot

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

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)

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.

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?

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,

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?

Try

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

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

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

Replies
3
Views
245
Replies
5
Views
353
Legacy 143009
L
Replies
1
Views
232
Replies
7
Views
267
Replies
8
Views
411

1,211,685
Messages
6,103,285
Members
447,853
Latest member
olddutch7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back