formula to repeat an equation over a certain number of columns

[RPM7]

Not sure how to phrase this but, is it possible to array a formula without having to repeat it?

I'm using the following formula to calculate the values of one particular field.

=(IF(AND($H$6="NO",(OR($O$10=$D$7,$O$10=$E$7,$O$10=$F$7))),0,IF(AND($H$6="YES",(OR($O$10=$D$7,$O$10=$E$7,$O$10=$F$7))),$N15*$O$13*$O$11,$N15*$O$13*$O$11)))+(IF(AND($H$6="NO",(OR($R$10=$D$7,$R$10=$E$7,$R$10=$F$7))),0,IF(AND($H$6="YES",(OR($R$10=$D$7,$R$10=$E$7,$R$10=$F$7))),$Q15*$R$13*$R$11,$Q15*$R$13*$R$11)))

However, I will be repeating this for 9 other fields and returning the sum of all of 10 fields together.
If I repeat the formula for the 10 fields, it gets rather cumbersome and would be difficult for another person to make sense of, if they needed to modify the spreadsheet in the future.

=(IF(AND($H$6="NO",(OR($O$10=$D$7,$O$10=$E$7,$O$10=$F$7))),0,IF(AND($H$6="YES",(OR($O$10=$D$7,$O$10=$E$7,$O$10=$F$7))),$N15*$O$13*$O$11,$N15*$O$13*$O$11)))+(IF(AND($H$6="NO",(OR($R$10=$D$7,$R$10=$E$7,$R$10=$F$7))),0,IF(AND($H$6="YES",(OR($R$10=$D$7,$R$10=$E$7,$R$10=$F$7))),$Q15*$R$13*$R$11,$Q15*$R$13*$R$11)))+(IF(AND($H$6="NO",(OR($U$10=$D$7,$U$10=$E$7,$U$10=$F$7))),0,IF(AND($H$6="YES",(OR($U$10=$D$7,$U$10=$E$7,$U$10=$F$7))),$T15*$U$13*$U$11,$T15*$U$13*$U$11)))+(IF(AND($H$6="NO",(OR($X$10=$D$7,$X$10=$E$7,$X$10=$F$7))),0,IF(AND($H$6="YES",(OR($X$10=$D$7,$X$10=$E$7,$X$10=$F$7))),$W15*$X$13*$X$11,$W15*$X$13*$X$11)))+(IF(AND($H$6="NO",(OR($AA$10=$D$7,$AA$10=$E$7,$AA$10=$F$7))),0,IF(AND($H$6="YES",(OR($AA$10=$D$7,$AA$10=$E$7,$AA$10=$F$7))),$Z15*$AA$13*$AA$11,$Z15*$AA$13*$AA$11)))+(IF(AND($H$6="NO",(OR($AD$10=$D$7,$AD$10=$E$7,$AD$10=$F$7))),0,IF(AND($H$6="YES",(OR($AD$10=$D$7,$AD$10=$E$7,$AD$10=$F$7))),$AC15*$AD$13*$AD$11,$AC15*$AD$13*$AD$11)))+(IF(AND($H$6="NO",(OR($AG$10=$D$7,$AG$10=$E$7,$AG$10=$F$7))),0,IF(AND($H$6="YES",(OR($AG$10=$D$7,$AG$10=$E$7,$AG$10=$F$7))),$AF15*$AG$13*$AG$11,$AF15*$AG$13*$AG$11)))+(IF(AND($H$6="NO",(OR($AJ$10=$D$7,$AJ$10=$E$7,$AJ$10=$F$7))),0,IF(AND($H$6="YES",(OR($AJ$10=$D$7,$AJ$10=$E$7,$AJ$10=$F$7))),$AI15*$AJ$13*$AJ$11,$AI15*$AJ$13*$AJ$11)))+(IF(AND($H$6="NO",(OR($AM$10=$D$7,$AM$10=$E$7,$AM$10=$F$7))),0,IF(AND($H$6="YES",(OR($AM$10=$D$7,$AM$10=$E$7,$AM$10=$F$7))),$AL15*$AM$13*$AM$11,$AL15*$AM$13*$AM$11)))+(IF(AND($H$6="NO",(OR(AP$10=$D$7,AP$10=$E$7,AP$10=$F$7))),0,IF(AND($H$6="YES",(OR(AP$10=$D$7,AP$10=$E$7,AP$10=$F$7))),AO15*AP$13*AP$11,AO15*AP$13*AP$11)))

Each field is is 3 columns apart.
Basically, I'm wondering is it possible to just write the first formula and say repeat 9 times every 3 columns?

NB: I will be copying the formula down for 15 rows, hence some of the cells aren't locked for copying down.

Any help would be greatly appreciated, I just don't know whether this is even possible but I've been surprised before with Excel.

 

[shg]

Doesn't the first formula simplify to ...

= IF(AND(H6="YES", OR(O10=D7, O10=E7, O10=F7)), N15*O13*O11)
+ IF(AND(H6="YES", OR(R10=D7, R10=E7, R10=F7)), Q15*R13*R11)

 

[RPM7]

Yup, you're absolutely right.
I had different conditions in the original format that returned error messages, but I had to delete them as I developed the spreadsheet more.
I didn't clean it up properly. Thanks for pointing it out.

 

[RPM7]

FYI: I had to make just a little tweak, but your formula is a massive improvement to my spreadsheet shg. Thanks.

= IF(AND($H$6="NO", OR($O$10=$D$7, $O$10=$E$7, $O$10=$F$7)),0, $N15*$O$13*$O$11)
+ IF(AND($H$6="NO", OR($R$10=$D$7, $R$10=$E$7, $R$10=$F$7)),0, $Q15*$R$13*$R$11)
+ IF(AND($H$6="NO", OR($U$10=$D$7, $U$10=$E$7, $U$10=$F$7)),0, $T15*$U$13*$U$11)
+ IF(AND($H$6="NO", OR($X$10=$D$7, $X$10=$E$7, $X$10=$F$7)),0, $W15*$X$13*$X$11)
+ IF(AND($H$6="NO", OR($AA$10=$D$7, $AA$10=$E$7, $AA$10=$F$7)),0, $Z15*$AA$13*$AA$11)
+ IF(AND($H$6="NO", OR($AD$10=$D$7, $AD$10=$E$7, $AD$10=$F$7)),0, $AC15*$AD$13*$AD$11)
+ IF(AND($H$6="NO", OR($AG$10=$D$7, $AG$10=$E$7, $AG$10=$F$7)),0, $AF15*$AG$13*$AG$11)
+ IF(AND($H$6="NO", OR($AJ$10=$D$7, $AJ$10=$E$7, $AJ$10=$F$7)),0, $AI15*$AJ$13*$AJ$11)
+ IF(AND($H$6="NO", OR($AM$10=$D$7, $AM$10=$E$7, $AM$10=$F$7)),0, $AL15*$AM$13*$AM$11)
+ IF(AND($H$6="NO", OR($AP$10=$D$7, $AP$10=$E$7, $AP$10=$F$7)),0, $AO15*$AP$13*$AP$11)

Also:
If anyone knows a way of repeating the formula across, I'd still be interested in learning about it.

Cheers.

 

[FDibbins]

Given shg's suggestion, what needs to adjust, and what needs to stay fixed, as you copy across?

Can you show what the copied formulas would look like?

 

[RPM7]

Sorry for all the confusion.

The last formula I posted works.
NB: I had to change the original from shg as I only had 3 values in my exclusion list, and 7 others that would be included regardless of the "Yes"/"No" criteria.

When my formula was complete, I copied it down for another 14 rows.

For row 1:

= IF(AND($H$6="NO", OR($O$10=$D$7, $O$10=$E$7, $O$10=$F$7)),0, $N15*$O$13*$O$11)
+ IF(AND($H$6="NO", OR($R$10=$D$7, $R$10=$E$7, $R$10=$F$7)),0, $Q15*$R$13*$R$11)
+ IF(AND($H$6="NO", OR($U$10=$D$7, $U$10=$E$7, $U$10=$F$7)),0, $T15*$U$13*$U$11)
+ IF(AND($H$6="NO", OR($X$10=$D$7, $X$10=$E$7, $X$10=$F$7)),0, $W15*$X$13*$X$11)
+ IF(AND($H$6="NO", OR($AA$10=$D$7, $AA$10=$E$7, $AA$10=$F$7)),0, $Z15*$AA$13*$AA$11)
+ IF(AND($H$6="NO", OR($AD$10=$D$7, $AD$10=$E$7, $AD$10=$F$7)),0, $AC15*$AD$13*$AD$11)
+ IF(AND($H$6="NO", OR($AG$10=$D$7, $AG$10=$E$7, $AG$10=$F$7)),0, $AF15*$AG$13*$AG$11)
+ IF(AND($H$6="NO", OR($AJ$10=$D$7, $AJ$10=$E$7, $AJ$10=$F$7)),0, $AI15*$AJ$13*$AJ$11)
+ IF(AND($H$6="NO", OR($AM$10=$D$7, $AM$10=$E$7, $AM$10=$F$7)),0, $AL15*$AM$13*$AM$11)
+ IF(AND($H$6="NO", OR($AP$10=$D$7, $AP$10=$E$7, $AP$10=$F$7)),0, $AO15*$AP$13*$AP$11)

For row 2:

= IF(AND($H$6="NO", OR($O$10=$D$7, $O$10=$E$7, $O$10=$F$7)),0, $N16*$O$13*$O$11)
+ IF(AND($H$6="NO", OR($R$10=$D$7, $R$10=$E$7, $R$10=$F$7)),0, $Q16*$R$13*$R$11)
+ IF(AND($H$6="NO", OR($U$10=$D$7, $U$10=$E$7, $U$10=$F$7)),0, $T16*$U$13*$U$11)
+ IF(AND($H$6="NO", OR($X$10=$D$7, $X$10=$E$7, $X$10=$F$7)),0, $W16*$X$13*$X$11)
+ IF(AND($H$6="NO", OR($AA$10=$D$7, $AA$10=$E$7, $AA$10=$F$7)),0, $Z16*$AA$13*$AA$11)
+ IF(AND($H$6="NO", OR($AD$10=$D$7, $AD$10=$E$7, $AD$10=$F$7)),0, $AC16*$AD$13*$AD$11)
+ IF(AND($H$6="NO", OR($AG$10=$D$7, $AG$10=$E$7, $AG$10=$F$7)),0, $AF16*$AG$13*$AG$11)
+ IF(AND($H$6="NO", OR($AJ$10=$D$7, $AJ$10=$E$7, $AJ$10=$F$7)),0, $AI16*$AJ$13*$AJ$11)
+ IF(AND($H$6="NO", OR($AM$10=$D$7, $AM$10=$E$7, $AM$10=$F$7)),0, $AL16*$AM$13*$AM$11)
+ IF(AND($H$6="NO", OR($AP$10=$D$7, $AP$10=$E$7, $AP$10=$F$7)),0, $AO16*$AP$13*$AP$11)

etc...


I'm just curious as to whether there is another way of doing this.
If I didn't have to copy the whole thing down 14 rows, I could have removed some of the fixed cell references, and just Ctrl+C & Ctrl+V across 9 times.

I had 2 choices, remove the fixed cell reference, copy across and then insert the $ to lock the formula and then drag it down to fill out all the rows.
or
I could have copied over the first formula while its fixed and then change out the cell references manually, and then drag it dow to fill out all the rows.


Is it even possible to just use the first reference to the formula and repeat it across every 3rd column 9 times?

= IF(AND($H$6="NO", OR($O$10=$D$7, $O$10=$E$7, $O$10=$F$7)),0, $N15*$O$13*$O$11)

This is for curiosity purposes now. I've been in this situation before and wondered if there was a solution.