Summing variable data across rigid boundaries

**LHUK** · 06-18-2009, 07:10 AM

I apologize if the title is not clear.

Its been a long while since ive used excel i am really struggling with what might be a basic problem, and im hopping someone here may be able to help... i shall proceed...

I shall use a basic example to illustrate my problem - see attached - as the spreadsheet im actually working on is huge and contains sensitive data.

So,

In the spreadsheet attached, the problem is: I am mowing the grass of a football pitch and getting paid by the m3 of grass i mow. however i'm getting paid more when i mow at greater distances - defined by certain 'reaches'. so i need to know how many m3 ive mowed in each reach.

I make a note of where I start on the pitch and where i end, and also how much grass i mow.

so im looking for a solution that is intelligent enough to distinguish where Ive taken grass from and how much, given that i know where i started, where i finish and how much i take.

I know this is long winded but ive been struggling with this all morning and getting know where

any help much much appreciated

**dominicb** · 06-18-2009, 07:27 AM

Good morning LHUK

I'm not sure if I've fully grasped what you want to do here, but have a look at the spreadsheet attached. I've added a UDF to your sheet, which you'll need to allow macros for it to work. This will count the yellow squares within each of your boundaries and return the number of yellow squares.

I think this is the direction we need to go in, but let me know if I'm barking up the wrong tree.

HTH

DominicB

**LHUK** · 06-18-2009, 08:34 AM

Good morning to you!

Thanks for taking the time to help.

yeah i guess i didn't fully explain myself. the yellow boxes were just an illustration of my problem. the bottom line is, its all about splitting up the volume correctly.

I've attached a modified example: where basically I have 2 Tables.

table 1 = my data.

table 2 = how i want to change the data.

(in table 2 Ive hand calc'd it so you can see what i mean....it seems like such a simple problem from the outset but its doing my head in lol. )

Again thank you so much for your bother.

Leigh

**LHUK** · 06-18-2009, 10:16 AM

Does anyone know if this is even do-able within excel?

otherwise Ill stop wasting my time and give up!

Thanks,

Leigh

**windknife** · 06-18-2009, 11:23 AM

Try this,

G8

Code:

=IF(AND(G$6>=$D8,G$6<=$E8),$F8/($E8-$D8+1),"")

copied down to P13.

U17

Code:

=SUM(OFFSET($G$8,0,Q17-1,10,R17-Q17+1))

copied down to U20.

You can also see attached file.

Hope this helps,
windknife

**LHUK** · 06-19-2009, 08:40 AM

Thanks for the replies,

like i said : i really shouldn't have put the yellow boxes in there!!! lol they were JUST a visual depiction of what i was trying to calc numerically in my 'real' spreadsheet.

anyway thanks for the inspiration

Ive used a whole bunch of IF AND statements to crack this nut, and I have since learned that Excel can handle a max of 7/8 per cell??!! so i had to use 2 columns.

column1 formula:

=IF(AND($D13=$AI$41,$E13=$AS$41),$F13,(IF(AND($D13<$AI$41,$E13>$AS$41),$F13*(($AS$41-$AI$41)/($E13-$D13)),(IF(AND($D13>$AI$41,$E13<$AS$41),$F13,(IF((AND($D13=$AI$41,$E13<$AS$41)),$F13,(IF((AND($D13<$ AI$41,$E13>$AI$41,$E13<$AS$41)),$F13*(($E13-$AI$41)/($E13-$D13)),(IF((AND($D13<$AI$41,$E13=$AS$41)),$F13*(($E13-$AI$41)/($E13-$D13)),I13)))))))))))

hidden column 2:

=IF(AND($E13=$AS$41,$D13<$AS$41,$D13>$AI$41),$F13,IF(AND($D13>$AI$41,$D13<$AS$41,$E13>$AS$41),$F13*( ($AS$41-$D13)/($E13-$D13)),IF(AND($D13=$AI$41,$E13>$AS$41),$F13*(($AS$41-$D13)/($E13-$D13)),0)))

attached is my solved example for those interested.

Thanks and take care,

Leigh