IF function

[greenfortyguy]

Hello,

I am trying to use the IF function to perform a calculation. I will try to outline the scenario below.

A sheet called 'Outputs' has monthly data, running from Sep-17 in cell T36 to Aug-27 in cell EI36.

Another sheet called 'Analysis', in which the formula is going, has the same monthly range but needs to return a value every 3 months (a sum of the 3 months in the 'Outputs' sheet). The first of these 3-month sums will be in cell V64 (Nov-17) of the 'Analysis' sheet. The second will be in Y64, the third in AB64 etc.

For cell V64 in 'Analysis', I am looking for a formula that will ask:

If the SUM(Outputs!T36:V36) is less than 40,000,000, multiply 40,000,000 by a constant (e.g. A1). If the SUM(Outputs!T36:V36) is greater than 40,000,000, multiply SUM(Outputs!T36:V36) minus 40,000,000 by another constant (e.g A2).


For cells Y64, AB64 and onwards, it is a little more complicated. For cell Y64, I am then looking for a formula that will ask:

If the SUM(Outputs!T36:Y36) is less than 40,000,000, multiply 40,000,000 by A1. If the SUM(Outputs!T36:Y36) is greater than 40,000,000, multiply the SUM(Outputs!W36:Y36) minus 40,000,000 by A2.


For clarity, cell AB64 would be:

If the SUM(Outputs!T36:AB36) is less than 40,000,000, multiply 40,000,000 by A1. If the SUM(Outputs!T36:AB36) is greater than 40,000,000, multiply the SUM(Outputs!Z36:AB36) minus 40,000,000 by A2.


Apologies for the length of this post. I hope I have been clear enough for somebody to help. If there was a way I could enter this formula in the first cell and drag to populate the other cells (perhaps using OFFSET?) that would be great.

Many thanks,

Guy

 

[AlanY]

this solution with helper rows 3 & 4.
copy E3 to E5 across, formula in E4 taking the advantage of a empty cell in B4, otherwise need amend to work.

row 1 is your monthly data and row 2 at 3 months interval. your required calculations in row 5.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	2		5,000,000	5,000,000	2,500,000	2,500,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	2,500,000	2,500,000
2	4				12,500,000			12,500,000			15,000,000			10,000,000
3					12,500,000			25,000,000			40,000,000			50,000,000
4					12,500,000			37,500,000			65,000,000			90,000,000
5					80,000,000			80,000,000			100,000,000			200,000,000
Sheet1

Cell Formulas
Range	Formula
E3	=IF(E2="","",SUM($E$2:E2))
E4	=IF(E3="","",SUM(B3:E3))
E5	=IFERROR(IF(E3<40000000,40000000*$A$1,(E4-40000000)*$A$2),"")

 

[greenfortyguy]

Hi Alan,

Thank you very much for this clear response.

Perhaps as a follow up question, how can I get rid of the #VALUE error occurring in some cells as a result of this formula:

=MIN(IF(T39>40000000,T36,""), IF(T39>40000000, T39-40000000))

I am assuming it is the ("") that is giving the MIN function a problem. Is there a way around this?

Many thanks for your help,

 

[AlanY]

you can try to trap that with the iferror() function like this

=iferror(MIN(IF(T39>40000000,T36,""), IF(T39>40000000, T39-40000000)),"")

or replace the "" with 0

=MIN(IF(T39>40000000,T36,0), IF(T39>40000000, T39-40000000))