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
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
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