Need help with exceptions

**jeanneuker** · 07-22-2014, 01:37 PM

i do not profess to be experienced ... however here is my formula ...

=IF(E2=0,"",IF(B4=0,"",IF(P2>2,(E3*90%*B4*E2),(E3*100%*B4*E2))))

the problem is that out of 13 columns (and three columns of totals) in which i have established the math correctly and everything works, BUT ...

there are 7 columns which must apply 100% to ONLY THE FIRST TWO when they are selected ... i cannot figure out how to tell these columns to apply 100% to only the first two ... hope this makes sense to someone ... thanks in advance for any help i receive!

**cbatrody** · 07-22-2014, 02:20 PM

Hi,

Welcome to the Forum.

It's difficult to comprehend "there are 7 columns which must apply 100% to ONLY THE FIRST TWO when they are selected". Can you please post a sample template in excel format using "Go Advanced" option.

**jeanneuker** · 07-22-2014, 02:45 PM

when you say "go to advanced" are you asking me to attach my file? ... here it is ...

**cbatrody** · 07-22-2014, 03:19 PM

Which are those 7 columns in the file that must apply 100% to only the First Two when they are selected?

**jeanneuker** · 07-22-2014, 03:22 PM

All those that are $86 ... D e f g i l m

**jeanneuker** · 07-22-2014, 03:23 PM

ALSO ... sorry ... after the first two, the other columns still need the 90% discount applied ...

**jeanneuker** · 07-22-2014, 03:31 PM

ALSO, sorry again ... the FIRST TWO can be ANY ONE of the SEVEN columns ... eeeek ... just to be clear ...

**cbatrody** · 07-22-2014, 03:36 PM

If I understand your formula correct, the logic behind "Quote" row is if the Book Quantity is more than 2, then 90% rate (Business Card) is applicable, otherwise 100%. Why would you separate out $86 columns here?

**jeanneuker** · 07-22-2014, 03:44 PM

well ... first ... column C and H are ALWAYS the same price ... not discounted ... columns J K N 0 are already discounted and never get the 90% ... but the D E F G I L M columns get the 90% discount ... HOWEVER ... the FIRST TWO books are ALWAYS the regular price ... i.e. $86 ... THEN the third book is ALWAYS discounted ... the problem is that if a client selects C and D, the way i have the formula set up D will not apply the 100% as one of the FIRST two books ... i know it's complicated ... i don't make the policy ... just trying to create this so all the calculation is done automatically ... whew!