Question about Excel 2007

[Chris L]

I've been recording some figures on a spreadsheet and would like to cut down on the amount of times I have to manually enter the same values on a worksheet.

The first 9 columns on the worksheet are made up of weigh-in dates, weight in kilos, weight in stones and pounds, weight in pounds, weight lost since last weigh-in, total weight lost to date, % of original weight lost and finally BMI.

At the moment I manually enter the new weight in columns C & D which causes the kilos column, total weight in pounds and BMI columns to automatically update accordingly.

I've tried to format columns F and G so that they also automatically update but because of the formulae I'm using values get entered for future weeks that have no value in the corresponding weight columns. Is there a way of formatting these two columns so that I don't get this problem and they will automatically update when I enter a new weight in columns C & D?

Finally, in another part of the sheet, two cell's are used to manually record the current weight in stones and pounds and the value entered into M2 is used to help calculate the number of points the person is allowed (Based on weight watchers system). Is there a way of making cell M2 automatically update the latest value entered in C column?

I have a sample of how it currently is but don't know of anyway to upload it sorry.

Any advice or help would be really appreciated Thanks in advance,

Chris

 

[giantwolf]

Pm sent with an offer to try and help tomorrow. If anyone can look earlier then I'm sure Chris would appreciate your help.

 

[irelanst]

I've tried to format columns F and G so that they also automatically update but because of the formulae I'm using values get entered for future weeks that have no value in the corresponding weight columns. Is there a way of formatting these two columns so that I don't get this problem and they will automatically update when I enter a new weight in columns C & D?

You need to use an IF function to look at the cells in column C & D;
=IF(OR(C4="",D4="")=TRUE,"",E3-E4)

Finally, in another part of the sheet, two cell's are used to manually record the current weight in stones and pounds and the value entered into M2 is used to help calculate the number of points the person is allowed (Based on weight watchers system). Is there a way of making cell M2 automatically update the latest value entered in C column?

In M2 put the following formula;
=LOOKUP(9.9999999999999+307,C:C)

Reading your explanation again I might have that arse about face; do you want a value entered into M2 to be added to the bottom of the list in column C? If so I would use the "Worksheet_Change" function to append the new entry to the list;

Right click on the tab at the bottom of your worksheet, and select ‘view code’
Paste the code below into the new window;

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
' Change Range to suit the cells needed e.g. "L3" "L3,M4,..."
Set rng = Intersect(Target, Range("M2"))
' If the selection isn't M2
If rng Is Nothing Then
Exit Sub
Else
' Runs this code
With Worksheets("Sheet1") ' The name of your worksheet with the data in
HeaderRows = 1 ' The number of header rows on your sheet
NewValue = .Range("M2").Value ' Copies the value you just entered to the variable NewValue
For Counter = 1 To 100000 ' Index used to count down column C
Check = .Cells(Counter + HeaderRows, 3) ' Looks in Column C to see whats there
If Check = "" Then ' Checks to see if it's empty
.Cells(Counter + HeaderRows, 3) = NewValue 'If it is writes the new value
Exit Sub
Else
End If
Next Counter ' goes to the next column
End With
End If

End Sub

Now when you type a value into M2 it will add it to the bottom of the list of values in column C

 

[Chris L]

Pm sent with an offer to try and help tomorrow. If anyone can look earlier then I'm sure Chris would appreciate your help.

Thanks very much for the PM and the offer to help I really do appreciate it Thanks to Steve's post I've been able to do what I wanted and can now see how to adapt it to do some other things

You need to use an IF function to look at the cells in column C & D;
=IF(OR(C4="",D4="")=TRUE,"",E3-E4)

Cheers for your post Steve

I've used this and also adapted it so that column B (total weight in kilos) also automatically updates with a value showing if the corresponding cells in C & D are empty. I'm guessing that the "" means a value, as in if C4 or D4 has a value in it (True) then perform the calculation I want in that cell?

In M2 put the following formula;
=LOOKUP(9.9999999999999+307,C:C)

This has also worked but can you explain what it's done so that I can try to understand? I'm guessing the LOOKUP command and C:C is saying to look at the whole of column C but I don't understand what the first part does?

Cheers,

Chris

 

[f1charlie]

In M2 put the following formula;
=LOOKUP(9.9999999999999+307,C:C)

I was intrigued by this too so I did a little searching.

It seems that if you use LOOKUP with a lookup value that's greater than any value that might appear in the column then the result is the last value in that column.

The largest value that you can enter in a cell in Excel is 9.99999999999999E+307 so the formula should actually be:

=LOOKUP(9.9999999999999E+307,C:C)

 

[irelanst]

I've used this and also adapted it so that column B (total weight in kilos) also automatically updates with a value showing if the corresponding cells in C & D are empty. I'm guessing that the "" means a value, as in if C4 or D4 has a value in it (True) then perform the calculation I want in that cell?

“” means empty cell, so OR(C4=””,D4=””) means if either C4 or D4 are empty the value returned is True.
The IF formula is IF(this logical test is true,then do this, or if it’s false then do this), so if either C4 or D4 are empty, then the formula answer is “” (a blank cell) if both C4 & D4 have a value, then the OR function is False so the formula becomes E3-E4. The IF(Cell=””,””,Formula) is used a lot to neaten up worksheets by not showing a cell value until all the arguments are entered (it hides #DIV/0 errors etc.)

I was intrigued by this too so I did a little searching.
It seems that if you use LOOKUP with a lookup value that's greater than any value that might appear in the column then the result is the last value in that column.
The largest value that you can enter in a cell in Excel is 9.99999999999999E+307 so the formula should actually be:
=LOOKUP(9.9999999999999E+307,C:C)

Good spot on me missing out the “E” (more haste, less speed) I would normally just pick any value much greater than the list will contain, in this case 100 should have been enough. Bang on with the explanation of how the function works.

 

[f1charlie]

Good spot on me missing out the “E” (more haste, less speed) I would normally just pick any value much greater than the list will contain, in this case 100 should have been enough. Bang on with the explanation of how the function works.

All I can say is thank goodness for Google as I would not have figured that out from the definition for LOOKUP -

"If the Lookup can not find an exact match, it chooses the largest value in the array that is less than or equal to the value."

Can you explain why the formula above always returns the last value in the column and not the largest value?

 

[Chris L]

Once again, thanks very much, not just for your help but also for explaining why it works

Could I ask another question? I'm now trying to adapt some of my new understanding regarding the IF function to set up an automated "Target Weight" column. I've had a go at writing a formula but whilst it works when a value is entered into a cell, if no value is entered then it displays the heighest target value rather than leaving the cell blank?

This is the formula, I've no idea how effecient it is so if anyone knows a simpler way to achieve the same result I'd love to hear it?

Target Number formula:

=IF(E3>=318,318,IF(E3>=279,279,IF(E3>=245,245,IF(E3>=214,214,IF(E3>=179,179,IF(E3>=157,"Done",""))))))

Once again, thanks in advance for any help or advice

Cheers,
Chris

 

[irelanst]

Can you explain why the formula above always returns the last value in the column and not the largest value?

For the LOOKUP function to work 100% correctly the data has to be sorted in ascending order, if the data isn’t in order it comes up with some strange results. It works by going step by step through a list of data (array) until it comes to a value that is either;
an exact match of the LOOKUP target, in which case the ‘answer’ is the target value.
Or
a value larger than the LOOKUP target, in which case the ‘answer’ is the preceding value.
If it reaches a value that is equal to or larger than the target it stops calculating so if column A has the following values;
1
2
3
4
5
The formula LOOKUP(3,A:A) will return 3
But if you change the cells to;
1
99
3
4
5
The same formula, LOOKUP(3,A:A) will return 1 (because it ‘gets to 99 which is greater than the target, before it even sees the 3)
If you change the formula to LOOKUP(100,A:A), then it gets to the bottom of the list before it finds a match, and the default is to return the last value: 5

If you wanted to search for a number, but if it isn’t in the data array return the largest number that is in the data array, you would need a combination of LOOKUP, MAX and IF functions (or write some code).

This is the formula, I've no idea how effecient it is so if anyone knows a simpler way to achieve the same result I'd love to hear it?
Target Number formula:
=IF(E3>=318,318,IF(E3>=279,279,IF(E3>=245,245,IF(E3>=214,214,IF(E3>=179,179,IF(E3>=157,"Done",""))))))

I’m guessing that there is a formula in E3 with, IF(Cell=””,””,Else) in it? In which case the new formula isn’t seeing the cell as blank, but is trying to assign a value to "" and is confusing itself. Try this;

=IF(E3="","",IF(E3>=318,318,IF(E3>=279,279,IF(E3>=245,245,IF(E3>=214,214,IF(E3>=179,179,IF(E3>=157,"Done","")))))))

It explicitly looks to see if E3 is "" first and if it is returns "" as the answer.

 

[Chris L]

I’m guessing that there is a formula in E3 with, IF(Cell=””,””,Else) in it? In which case the new formula isn’t seeing the cell as blank, but is trying to assign a value to "" and is confusing itself. Try this;

=IF(E3="","",IF(E3>=318,318,IF(E3>=279,279,IF(E3>=245,245,IF(E3>=214,214,IF(E3>=179,179,IF(E3>=157,"Done","")))))))

It explicitly looks to see if E3 is "" first and if it is returns "" as the answer.

Thanks for the quick reply

The current value in E3 is 353 so H3 should return 318 as the target. I've just tried the formula in H3 and it now reports the value TRUE instead of 318. The formula currently in E3 is this:

=IF((D3="")=TRUE,"",SUM(D3,(C3*14)))

 

[f1charlie]

Thanks Steve. Why can't they explain it like that in the help file?

 

[Chris L]

Thanks for the quick reply

The current value in E3 is 353 so H3 should return 318 as the target. I've just tried the formula in H3 and it now reports the value TRUE instead of 318. The formula currently in E3 is this:

=IF((D3="")=TRUE,"",SUM(D3,(C3*14)))

Sorry Steve, the formula works perfectly, the problem was with the user trying to apply it :bonk:

Thanks again for the help

 

[irelanst]

Crossed post with PM, but here's a different way to skin the cat using LOOKUP again;

In a spare area of a worksheet (I always set up a separate worksheet for ‘tables’ of lookup data) lets assume for this that in column A in a worksheet called TABLES you enter your boundary values;

179
214
245
279
318

Now in your cell H3 enter the following formula;
=IF(E3="","",IF(E3<=TABLES!$A$1,"DONE",LOOKUP(D3,TABLES!$A$1A$5)))
The 'smiley' should be ": $" without the quotes and space, I can't get rid of it!!!

The advantage is it is easier to alter the boundary values without having to change all you equations, try to keep numbers out of formulas if you can, use cells references or named ranges instead.

To save you a bit of time, in the formula, =IF((D3="")=TRUE,"",SUM(D3,(C3*14))) you don't need the =TRUE bit, IF(D3="","",SUM(D3,(C3*14)) does the same thing.

I didn't realise I was quite so nerdy when it comes to Excel!!!

 

[Lynton]

some good advice there, always more than 1 way to skin an excel cat.

Just a quickie.. we still have office 2003 at work, and will be migrating to 2010 this autumn. I assume it is backwards compatible with formulae and VBA, however the conditional formatting is a lot better in later editions.

 

[Chris L]

Crossed post with PM, but here's a different way to skin the cat using LOOKUP again;

In a spare area of a worksheet (I always set up a separate worksheet for ‘tables’ of lookup data) lets assume for this that in column A in a worksheet called TABLES you enter your boundary values;

179
214
245
279
318

Now in your cell H3 enter the following formula;
=IF(E3="","",IF(E3<=TABLES!$A$1,"DONE",LOOKUP(D3,TABLES!$A$1A$5)))
The 'smiley' should be ": $" without the quotes and space, I can't get rid of it!!!

The advantage is it is easier to alter the boundary values without having to change all you equations, try to keep numbers out of formulas if you can, use cells references or named ranges instead.

To save you a bit of time, in the formula, =IF((D3="")=TRUE,"",SUM(D3,(C3*14))) you don't need the =TRUE bit, IF(D3="","",SUM(D3,(C3*14)) does the same thing.

I didn't realise I was quite so nerdy when it comes to Excel!!!

Thanks very much again. I've now changed it to use LOOKUP and created a new worksheet called Tables. I had a little problem at first as I didn't understand why it was referring to D3 but I think the formula should read

=IF(E3="","",IF(E3<=Tables!$A$1,"DONE",LOOKUP(E3,Tables!$A$1A$5)))

rather than D3?

Once again, thanks very much Steve for your help