Array around Slope and Intercept Formula

[Debbie Thomson]

Hello all......I'm hoping someone can help me with an array formula please. I'm trying to put in this formula:

=SLOPE($C$2:$C$7602,$B$2:$B$7602)*B2-ABS(INTERCEPT($C$2:$C$7602,$B$2:$B$7602))

but with an array around it. I've attached an example of my file and what I'm after is an IF statement around it to say that I want the =SLOPE etc formula to calculate IF($A$2:$A$28=A2).

Thanks in advance.

[:) Sixthsense :)]

Instead of giving your formula and asking us to do change something please describe what you are trying to achieve by showing the expected output.

[Shotlod]

Sorry....I've added in the output in the new attachment that I'd like to see, but instead of putting in the absolute cell references within the formula, I really want to use an array around the whole of the reference.

Thank you very much.

[:) Sixthsense :)]

Thanks for showing the clear picture of your requirement.

In D2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=SLOPE(IF($A$2:$A$28=A2,$C$2:$C$28),IF($A$2:$A$28=A2,$B$2:$B$28))*B2-ABS(INTERCEPT(IF($A$2:$A$28=A2,$C$2:$C$28),IF($A$2:$A$28=A2,$B$2:$B$28)))

Drag it down…

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

[Shotlod]

That's brilliant, thank you so much for your help.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback and rep

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Shotlod]

Thank you, it was a great help. I'm currently logged in at home however, and don't have the option to 'solve' in the Thread Tools, I assume, until I log back in from work tomorrow. Immediately I get into work in the morning though, I'll do it.