To Whom it Concerns,
I've got 3 different tables for 3 different firms, I've created 3 IF statements which each select a value if the value is below a certain amount it multiplies it by the lowest value in the table; if not it will multiply it by the highest value.
The issue is each IF statement is relevant to a different table. My spreadsheet has a column for 'Firm name'; I need a formula that will look at the firm name and choose the relevant IF statement for the relevant firm.
So far my formula looks something like this:
=IF((AND(FY9="Firm 1")*(FE9<500000)),(FE9*'Legal Fees'!$E$29),(MASTER!FE9*'Legal Fees'!$E$35)OR(IF((AND(FY9="Firm 2")*(FE9<500000)),(FE9*'Legal Fees'!$E$42),(MASTER!FE9*'Legal Fees'!$E$48)OR(IF((AND(FY9="Firm 3")*(FE9<500000)),(FE9*'Legal Fees'!$E$55),(MASTER!FE9*'Legal Fees'!$E$61))
This doesn't seem to work for 2 reasons:
1) I don't seem to have joined each IF statement correctly using the OR function (not sure where i'm going wrong)
2) currently I have the 'AND' function for the firm name only applying to where the initial value is less than 500000; it should be " IF Firm 1 then IF lower that 500000 pull lowest value from Firm 1 Table, if not pull highest value from firm 1 table, IF Firm 2, IF initial value is less that 500000 pull lowest value from Firm 2 table, if not pull highest value from Firm 2 table, IF firm 3, IF value is less that 500000 pull lowest value from Firm 3 table, if not pull highest value from 'Firm 3' table.
Finding this pretty difficult and it would be great if I could get some help as soon as possible!
Many Thanks
HaczN12
I've got 3 different tables for 3 different firms, I've created 3 IF statements which each select a value if the value is below a certain amount it multiplies it by the lowest value in the table; if not it will multiply it by the highest value.
The issue is each IF statement is relevant to a different table. My spreadsheet has a column for 'Firm name'; I need a formula that will look at the firm name and choose the relevant IF statement for the relevant firm.
So far my formula looks something like this:
=IF((AND(FY9="Firm 1")*(FE9<500000)),(FE9*'Legal Fees'!$E$29),(MASTER!FE9*'Legal Fees'!$E$35)OR(IF((AND(FY9="Firm 2")*(FE9<500000)),(FE9*'Legal Fees'!$E$42),(MASTER!FE9*'Legal Fees'!$E$48)OR(IF((AND(FY9="Firm 3")*(FE9<500000)),(FE9*'Legal Fees'!$E$55),(MASTER!FE9*'Legal Fees'!$E$61))
This doesn't seem to work for 2 reasons:
1) I don't seem to have joined each IF statement correctly using the OR function (not sure where i'm going wrong)
2) currently I have the 'AND' function for the firm name only applying to where the initial value is less than 500000; it should be " IF Firm 1 then IF lower that 500000 pull lowest value from Firm 1 Table, if not pull highest value from firm 1 table, IF Firm 2, IF initial value is less that 500000 pull lowest value from Firm 2 table, if not pull highest value from Firm 2 table, IF firm 3, IF value is less that 500000 pull lowest value from Firm 3 table, if not pull highest value from 'Firm 3' table.
Finding this pretty difficult and it would be great if I could get some help as soon as possible!
Many Thanks
HaczN12