challenging formula

jo_park

New Member
Joined
Jun 27, 2012
Messages
16
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
 

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))
 
Upvote 0
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.
 
Upvote 0
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, :)
 
Upvote 0
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?
 
Upvote 0
Try

=IF(AND($AK9>0,$K9<>"Van"),$AN$3*$AK9,IF(OR($AK9={"",0}),$AJ9,$AK9)*$AR$3)
 
Upvote 0
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
 
Upvote 0
sorry should say, it pulled a value but not correct? based on calculation it's out by roughly $90
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top