IF Formula Query

[broncos347]

Can anyone help me?

I have a formula to calculate risks (see below), however, I have only managed to do one column of a table and have a further 4 columns to go. If I copy any paste the formula so far in to the formula it says the formula is to long.

What I would like to do is complete the formula for the register is to change the cells B6, D8 and D6. B6 and D8 contain text while D6 contains a number.

=IF(D4='Risk Assessment Matrix'!B6,IF('Risk Log'!E4='Risk Assessment Matrix'!D8,'Risk Assessment Matrix'!D6),IF(D4='Risk Assessment Matrix'!B5,IF('Risk Log'!E4='Risk Assessment Matrix'!D8,'Risk Assessment Matrix'!D5),IF(D4='Risk Assessment Matrix'!B4,IF('Risk Log'!E4='Risk Assessment Matrix'!D8,'Risk Assessment Matrix'!D4),IF(D4='Risk Assessment Matrix'!B3,IF('Risk Log'!E4='Risk Assessment Matrix'!D8,'Risk Assessment Matrix'!D3),IF(D4='Risk Assessment Matrix'!B2,IF('Risk Log'!E4='Risk Assessment Matrix'!D8,'Risk Assessment Matrix'!D2),IF(D4='Risk Assessment Matrix'!B1,IF('Risk Log'!E4='Risk Assessment Matrix'!D8,'Risk Assessment Matrix'!D1),

 

[MarkAndrews]

To my knowledge you can only have 7 IF statements in any IF formula

 

[fairwinds]

Hi,

I do not understand what you want to change but I believe your current formula can be shortened to:

=IF('Risk Log'!E4='Risk Assessment Matrix'!D8,INDEX('Risk Assessment Matrix'!D1:D6,MATCH(D4,'Risk Assessment Matrix'!B1:B6,0)))

Thus you might be able to adapt your changes without make the formula too long.

 

[broncos347]

Fairwinds, nearly there. However, I need to pull a figure out of the D1 to D6 range. The figures are 1, 3, 6, 10, 14, 16.

The current formula you have given returns a "FALSE".

 

[fairwinds]

The current formula you have given returns a "FALSE".

That is because 'Risk Log'!E4 "does not equal" 'Risk Assessment Matrix'!D8 and I don't know what should happen if that is the case. Add this to the end of the formula.