Averages formula

**Markshr** · 09-01-2010, 03:06 AM

I need a formula to calculate averages, from averages.

In column “A” I have different times in minutes and in column “B” I have average rainfall for that time.

Column “A” consists of “5min”, “6min”, “10min”, “20min”

Column “B” consists of “253mm”,”236mm”, “189mm”,”134mm”

What I require is,

If a user inputs a value for rainfall into cell “D1”, say “248mm” it can calculate the average time based on the above data.

Thanks

**peterjuhnke** · 09-01-2010, 06:16 AM

Hello
Please attach a copy with an example of what you are after, I assume you are using 2003!
To attach a copy, when adding comments, scroll down and find "Manage Attachments" and follow the instructions.
PS You'll find it easier if you already know where to find your sheet.
Regards
Peter

**shg** · 09-01-2010, 08:26 AM

You're making a rod for your back by putting the units in the cells. Enter numbers, and put the units elsewhere.

**DonkeyOte** · 09-01-2010, 08:39 AM

or, if the units are consistent use a Custom Format - eg: #"mm"
(this way only the numeric value resides in the cell but the "mm" still prints/displays)

**Markshr** · 09-01-2010, 05:01 PM

HI,

Thanks for the reply.

I have attached an example of what i am trying to achieve.

It is unitless.

The user inputs their recorded rainfall and the formula will average and provide the time based on the allready known data.

Thanks

**peterjuhnke** · 09-01-2010, 05:17 PM

Sorry I'm still unsure what you would like to see in columns D and F please give me an example.
Regards
Peter

**Markshr** · 09-01-2010, 05:29 PM

Hi,

THanks for your trouble.

Column D is where the user inputs the data and column F will contain the formula.

For example:

A person could record a rainfall and input it into column D.

Then based on the data, it could average and show the time frame for this rainfall measurement and nominate the time it could have occured in.

ie User input could be "190"mm, The formula would average out the time, around 8min or so.

Hope i am making sense.

Thanks

**peterjuhnke** · 09-01-2010, 05:47 PM

Sorry mate, it don't rain to often in Kalgoorlie, the info in a,b is it static or does it get added to are these past example or are they a guide/ standard? do you want to only input into d3 and see the result, is d3 the only input or will it continue down the column
You say an input of 190 in d3 should return approx 8 in F, were would you like it to get the info from?
Regards
Peter

**Markshr** · 09-01-2010, 07:35 PM

Thanks again for your interest.

Columns A and B contain fixed rainfall data that will not change or increase.

The user input cell, D3, allows the user to input their recorded rainfall of an unknown time.

The cell F3, then will calculate or average the time this rainfall took from the fixed rainfall data.

The result is only to appear in F3, and will only be based on the user input from D3.

I’m hoping I’m making sense again, sounds ok in my head!

**peterjuhnke** · 09-03-2010, 06:58 AM

Sorry I still don't understand what is what, is it saying that after 5min there was 253mm of rain in the gauge, if so, why is it that after 6min there is 236mm in the gauge, after 10min,189mm and after 20min there is 134mm???
How can there be getting less in the gauge???

Any way I don't know if this is what you are after or not, if it is then what rate did you want to work with outside these parameters, e.g. if the input was 100mm or 400mm?? I have left all the working logic so you can see how it all links together, if this is what you are after then the formula can be made to incorporate all of the individual formulas, therefore removing every thing within the red border, but this would be a fair bit of effort, so me not knowing if it is correct or if it is critical to have it consolidated I wouldn't bother.
Regards
Peter

**Markshr** · 09-03-2010, 07:17 AM

Peter,

I can't believe the effort you have gone to and the knowledge you posses in excel.

The data is rainfall intensity over a duration. So not a volume. Rainfall intensity decreases over time.

Everything you have done in "modified 2" is just what i am after.

As for the parameters, for what i require, the user input will not be greater or lower than the fixed data.

Thanks!

**peterjuhnke** · 09-03-2010, 06:54 PM

Your welcome
Do the formulas need to be consolidated or is it fine the way it is?
Peter

**Markshr** · 09-03-2010, 07:46 PM

Nope, it is perfect the way it is. Thanks!

**Neophema** · 04-19-2011, 10:41 PM

I am trying to track reoccurring values by time - events occurring each day, at the same time, averaged by time to illustrate (chart) the values.

This spreadsheet has events occurring at the same time each day, multiple days. I am trying to create a chart which tracks average these values, regardless of date.

See my example spreadsheet.