If Formula with a "BUT"

[pigsfoot]

HI, Apologies for subject title but not really sure how to explain what i'm looking to do in a different way.

This is an extract of some of the sheet

GPS Match to GEO.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2						Min GPS Match to GEO	0.39
3						Avg % based on total cell count	43%
4						Total cells with data	18
5				GEO	Actual GPS	Est GPS based on min req.		%Diff using min req. algorithum		GPS with Avg % Uplift AND Min 1/3 match	Diff between Calculated GPS and GEO
6	01-Jul-22	Friday		11835	37	4616		0%		6618	-5217
7	02-Jul-22	Saturday		11827	2791	4613		0%		6614	-5213
8	03-Jul-22	Sunday		6957	7729	2713		-10%		3890	-3067
9	04-Jul-22	Monday		10103	6782	3940		49%		9725	-378
10	05-Jul-22	Tuesday		9509	8115	3709		17%		11636	2127
11	06-Jul-22	Wednesday		9443	8525	3683		11%		12224	2781
12	07-Jul-22	Thursday		12333	7656	4810		61%		10978	-1355
13	08-Jul-22	Friday		10518	7484	4102		41%		10731	213
Loughborugh

Cell Formulas
Range	Formula
G3	G3	=SUM(H6:H36)/G4
G4	G4	=COUNTIF(H6:H36,">0")
J6:J13	J6	=IF(H6>0,(E6*$G$3)+E6,(F6*$G$3+F6))
K6:K13	K6	=J6-D6
F6:F13	F6	=D6*$G$2
H6:H13	H6	=IF(E6>F6,(D6-E6)/E6,0)

Basically I am looking to work out an average % increases as per column J which is based on an If statement that uses actual data if the min requirement is reached which is GPS number has to be a minimum of 39% of the GEO data, if not we based the % uplift on the estimated number ( column F ). However.... some times the GPS data is more than GEO as per row 8.. in this case i want to use the actual GPS number.

So in human terms i need an If statement that does what its currently doing with a "but" at the end that says if the GPS number is more than GEO use that instead... this formula would replace the current one used in column J

Hopefully that makes sense

 

[etaf]

how about
=IF(H6>0,(E6*$G$3)+E6,(F6*$G$3+F6))

=IF( E6>D6, E6, IF(H6>0,(E6*$G$3)+E6,(F6*$G$3+F6)))

some times the GPS data (Column E ) is more than GEO (Column D) as per row 8.. in this case i want to use the actual GPS number.

I think thats what you mean - BUT I maybe wrong , as not clear what cells we are looking at

 

[pigsfoot]

@etaf That works perfectly thank you, still trying to wrap my head around what the formula does but its working just the way i need it to. Thanks for your help.

 

[etaf]

the IF() works like
Left to right
IF ( TEST , do if TRUE , do if FALSE )
then you can nest a formula
IF( TEST , do if TRUE , now for the FALSE part of the test , add IF( TEST , if TRUE , if FALSE ))
and so on

so first we test to see if E6 is greater than D6 - if its true , then use the contents of cell D6 - if its false then go to the next section after the comma ,
which we have another IF ()
so execute that IF ()
Is H6 > 0 - if it is then do the true part ,(E6*$G$3)+E6
if its not TRUE then do the FALSE part
,(F6*$G$3+F6

does that help at all - if not i can try to explain again , if you let me know which bit is not clear

 

[pigsfoot]

Very helpful, thank you @etaf. That explanation make it very clear and actually very simple to understand. Much appreciated.

 

[etaf]

you are welcome