If statement with 6 conditions

[miss_gg]

Hi there,

I'm trying to build a report template that identifies the amount threshold based on category of project, and applies the discount to it.

There are 3 categories: fee, production and travel

The discount should only apply to amounts associated with fee.

Current 'If' formula I have in there takes into account invoice amounts only.
I can't seem to figure out how to make it check the credit notes (ie. minus figures) and apply the same logic.

I've attached a sample sheet; tab 1 - discount info, tab 2 - sample report sheet

Could you help me with formula on column I?

[XLent]

perhaps just use the ABS and SIGN to simplify?

I2: =IF($D2<>"Fee",0,LOOKUP(ABS($H2),{0,350000,750000,1500000},{0,5,6,7}%)*SIGN($H2))

copied down

[miss_gg]

Thanks, I tried it but it doesn't seem to read the values for Fee?
It shows as #REF!
Attachment 671291

[mgselin]

Try this formula out -

=IF(D2="Fee",IF(H2>=1500000, 7%, IF(H2>=750000, 6%, IF(H2>=350000, 5%, IF(H2>=1, 0%, IF(H2<=-1000000,-7%,IF(H2<=-750000,-6%,IF(H2<=-350000,-5%))))))), H2*0%)

That is my best guess at what you are looking for.

[XLent]

Thanks, I tried it but it doesn't seem to read the values for Fee?

refer attached - original sample with formula in place (I2)

[mgselin]

refer attached - original sample with formula in place (I2)

I like the use of "SIGN." I have never used that function before, nor did I know it existed.