[davidmcb]

Greetings,

I have no VBA or other Excel coding experience, but need a couple simple routines.

1. I need a certain cell (A) to equal the result of a certain
calculation. The calculation could return any positive number, but
the maximum value of A is 100,000 (storage capacity of a water tank).
By what formula can I insure that the value of A is < than or = to
100,000? Just to be clear, if the calculation returns 99,999, then that is what A should store.
However if the calculation returns 100,001, then A must store 100,000

2. In a sheet related to the one at issue above, I store daily water
inflow and outflow meter readings. I then subtract today's meter readings from
yesterday's to calculate daily inflow and outflow, and then go on to calculate and store
month-to-date and trailing month's water usage, etc.

The problem is that I do not always get the meter readings daily. I want Excel
to recognize when data is missing, do the subtraction based on today's and the
most recent earlier readings. then do a simple average based on the number
of days of missing data and store the averaged result in each of the cells for
the daily results.

Suggestions? Thank you!

[Advertisements]

Hi David

1. I need a certain cell (A) to equal the result of a certain
calculation. The calculation could return any positive number, but
the maximum value of A is 100,000 (storage capacity of a water tank).
By what formula can I insure that the value of A is < than or = to
100,000? Just to be clear, if the calculation returns 99,999, then that is what A should store.
However if the calculation returns 100,001, then A must store 100,000

This could be:

=IF(SUM(Cell1+Cell2)>100000,100000,SUM(Cell1+Cell2))

2. In a sheet related to the one at issue above, I store daily water
inflow and outflow meter readings. I then subtract today's meter readings from
yesterday's to calculate daily inflow and outflow, and then go on to calculate and store
month-to-date and trailing month's water usage, etc.

The problem is that I do not always get the meter readings daily. I want Excel
to recognize when data is missing, do the subtraction based on today's and the
most recent earlier readings. then do a simple average based on the number
of days of missing data and store the averaged result in each of the cells for
the daily results.

I'm not sure I understand what you're looking for.

If you don't have today's readings, which Excel can recognise, how can Excel "do the subtraction based on today's and the most recent earlier readings" if today's readings are missing?

You could, for example, have a formula that recognises today's readings are missing, then calculates an average of the, say, past seven days' readings, and enters that figure.

Can you explain further?

[Vino Rosso]

Hi David

1. I need a certain cell (A) to equal the result of a certain
calculation. The calculation could return any positive number, but
the maximum value of A is 100,000 (storage capacity of a water tank).
By what formula can I insure that the value of A is < than or = to
100,000? Just to be clear, if the calculation returns 99,999, then that is what A should store.
However if the calculation returns 100,001, then A must store 100,000

This could be:

=IF(SUM(Cell1+Cell2)>100000,100000,SUM(Cell1+Cell2))

2. In a sheet related to the one at issue above, I store daily water
inflow and outflow meter readings. I then subtract today's meter readings from
yesterday's to calculate daily inflow and outflow, and then go on to calculate and store
month-to-date and trailing month's water usage, etc.

The problem is that I do not always get the meter readings daily. I want Excel
to recognize when data is missing, do the subtraction based on today's and the
most recent earlier readings. then do a simple average based on the number
of days of missing data and store the averaged result in each of the cells for
the daily results.

I'm not sure I understand what you're looking for.

If you don't have today's readings, which Excel can recognise, how can Excel "do the subtraction based on today's and the most recent earlier readings" if today's readings are missing?

You could, for example, have a formula that recognises today's readings are missing, then calculates an average of the, say, past seven days' readings, and enters that figure.

Can you explain further?

[davidmcb]

Thank you for your reply. I understand your solution to my first question!

Regarding the second, When I have data for consecutive days, say day 1 and day 2, the subtraction (day 2 meter reading minus day one meter reading) occurs. If data for day 3, 4, etc are not entered, nothing should happen. However when data for day X is entered, I want Excel to do the subtraction day X minus day 2 (where day 2 just means the most recent past day with data entered), then do the average by dividing the subtraction (day x minus day 2) by the number of days between day X and day 2, and then enter that averaged figure in the cells between day X and day 2, including day X. Is this clear?

Thanks again! david

[Vino Rosso]

Trying to do this avoiding macros... here's where I am so far however I may be overcomplicating things. By posting this, someone may chip in with a better idea. I'm not quite there yet!

Create four column headings in row 1 - A=Readings; B=Difference; C=Previous Reading; D=Day Count

Enter increasing data in column A (A2 to A14) to represent readings increasing
2, 4, 5, 7, 12, 19, 22, 24, <blank>, <blank>, <blank>, 34, 38

In Column C, we want to show the previous reading when none was taken on the day
In cell C3, type =INDEX($A$2:$A2,MATCH(9.99999999999999E+307,$A$2:$A2))
Replicate/drag this formula down to C14 (in line with the 38 in column A)
The formula in C14 should be =INDEX($A$2:$A13,MATCH(9.99999999999999E+307,$A$2:$A13))

In column D, we want to count the days since the previous reading changed
In cell D3, type =COUNTIF($C$2:$C3,$C3)
Replicate/drag this formula down to D14 (in line with the 38 in column A)
The formula in D14 should be =COUNTIF($C$2:$C14,$C14)

In column B, we want to calculate the difference if a reading was taken or the average if not taken for a few days
In cell B3, type =IF($A3="","",SUM($A3-$C3)/$D3)
Replicate/drag this formula down to D14 (in line with the 38 in column A)
The formula in B14 should be =IF($A14="","",SUM($A14-$C14)/$D14)

The above gives a daily difference in readings and an average difference on the day a reading is taken following a few missed days but it doesn't put that average difference in the cells of the previous days. That is, A10 to A12 are blank. B13 calculates the current reading of 34 less the previous reading of 24 and divides this by four days to get 2.5, shown in B13. If I understand, you want this 2.5 to then appear in B10 to B12?

[Vino Rosso]

What we need is the formula in column B =IF($A3="","",SUM($A3-$C3)/$D3) to say:

=IF($A3="",look down this column and find the next non-blank value,SUM($A3-$C3)/$D3) or something like that.

It will probably need to be error trapped to allow for no values being available further down the column.

[davidmcb]

OK, this is a challenge for me. I will need to study it and work through Excel Help to try to understand. Guess this is harder than I thought. I will be away until tomorrow night.

Thank you very much!

[davidmcb]

OK, major progress, Vino Rosso. Based on your formula suggestion, I created
=IF(SUM(B13:B17)-B11>100000,100000,SUM(B13:B17)-B11)

Which works perfectly! Why Excel does not have an "ELSE" function seems strange , but never mind, this works. I can use this technique elsewhere!

I have not had time to study the other issue, but just reading over your work, I don't fully understand. I will work on it more soon.

Thank you!

[davidmcb]

OK, I have tried to think about the other problem in a more structured way.

Cells in column B receive data entry of meter readings.
Cells in column C calculate the simple subtraction of one day's meter reading from teh previous day's meter reading. so far, so simple.

Let's start with cell B1. First day's positie integer data is entered.
Cell C1 will be inert, since there is no data to subtract before cell B1
However Cell C2 will try to subtract B2 (which is empty of data) from B1 and will display -B1 vaue. This needs to be supressed, perhaps by IF(B2=0,0,SUM(B2-B1))

However, we also need to solve the data gap problem.

B1 has data, C1 no meaning, C2 =0
When B2 has data, C2 will display SUM(B2-B1)

Two days pass with no data... B3 will display 0, B4 should be blank.

B5 receives data. Now the formula in C5 must perform its magic.

1. check cell B4. If B4 has data, perform, SUM(B5-B4)
2. since B4 has no data (what is the meaning of no data? Is blank the same as zero?)
3. set a counter in Cell X1 and increment to 1
4. check cell B3. No data, increment counter in Cell X1 to 2
5. check cell B2. At last, data!!! Increment counter in Cell X1 to 3
6. perform SUM(B5-B2) and divide by Cell X1
7. enter result in cells C3, C4, and C5
8. delete counter data in X1

I hope this will make it easier for you to help me and for me to learn from you.

Next problems are:

1) how to sum current month's data, from 1 to 28, 29, 30 or 31, and then start over at 1 of the next month;

2) how to maintain a sum of the trailing 30 days

Thanks,

david

[Jonesey]

Excel's IF statement contains implied "Else" clauses built in - this is what the commas represent.

If you type in something like =IF(G16>0,"yes",IF(G16<1,"No",0))

You have an implied if - then - else statement - the FIRST comma is equivalent to "Then", the SECOND comma in the above represents the "Else", as is the third.

[davidmcb]

Thank you, Jonesey, I understand your explanation. dd

[Vino Rosso]

Is there a maximum number of days, say six, where there may be no readings taken or could it be any number?

However Cell C2 will try to subtract B2 (which is empty of data) from B1 and will display -B1 vaue. This needs to be supressed, perhaps by IF(B2=0,0,SUM(B2-B1))

Yes, =IF(B2="","",SUM(B2-B1)) can be used.

[davidmcb]

The max number of days is probably 100. During the winter, water issues are not critical so I seldom check our water system. During the summer I check at least every few days. Even so, for completeness of data, I would like Excel to average and fill data as described. Can what I want be done?

Thx

[inapaler]

I am learning here!
I like the If mesh statement, in replacement of marco.

by the way why replace macro?