🔒 Locked

Excel Help

Started by DigitalChaos Oct 11, 2004 at 7:41 PM 19 replies 1.2k views

Original Post

131

Oct 11, 2004 at 7:41 PM

If anyone here is knowledgeable in Excel, I need some help. In cells, I want a letter to represent a numerical value. So maybe I want the letter A to represent the number 4. So wherever I put the letter A in, it will see it as 4 when used in an equation in another cell. I've looked through the functions and don't see any that would help me. I'm kinda new to Excel. Can you create your own functions? Maybe a function like this: define(numerical_value, letter_to_display) Where letter to display is what you see in the cell, but if used in an equation, it will use it's numerical value in the equation. Before anyone starts acusing, this is not a homework question. I'm just trying to figure it out. Any help is appreciated. Thanks.

32

Oct 11, 2004 at 7:58 PM

The usual way I do this is to put the numeric value in a random cell somewhere out of the way, and then just refer to it. This means that you would have C34 instead of 3.14 if you put 3.14 in cell C34. This has the advantage that the cell can be easily changed at any time. This is especially useful if you are looking at net present value or depreciation and you want to have many formula's referring to a single constant.

Author

131

Oct 11, 2004 at 8:04 PM

I don't want it to be out of the way type of thing. I'm setting up a sheet to determine my GPA. So A=4, B=3, C=2, D-1, F=0. So I want to put A in the cell instead of 4. But when I add them together to get the sum, I want it to add 4 since that's what an A is. I know there must be a way, I've been looking for about 2 hours now with zero luck :(

183

Oct 11, 2004 at 8:19 PM

In OpenOffice.org you can give a cell (or group of cells) a name with Ctrl+F3 or Insert-->Names-->Define, and then you can reference them by name. I'm sure there's something similar in Excel.

Author

131

Oct 11, 2004 at 8:22 PM

yeah, you can give them a name, but that's not what I need. when you give it a name all you're doing is giving it a name. The value in the cell stays the same. I want a cell to display the letter 'A' but then have a numerical value of 4 so when I add cells together I dont' get that stupid #VALUE! error. I know there must be a way, and I know someone knows how to do it. Just lookin for some help.

183

Oct 11, 2004 at 8:31 PM

Oh, sorry. The function to do that in OpenOffice is INDIRECT.

Say A had a value of 4, B has a value of A, and C has a value of =INDIRECT(B), C will end up with a value of 4. Yeah, again this isn't Excel, but there's probably something similar.

Author

131

Oct 11, 2004 at 8:58 PM

yeah, same function in Excel. It seems it references to another cell. Dont think that's what I'm looking for.

183

Oct 11, 2004 at 9:42 PM

... ? Why wouldn't you want to reference a cell? Where else would you keep your data in a spreadsheet?

Anywho, what about LOOKUP?

I'm sure INDIRECT was exactly what you asked for, though.

Author

131

Oct 11, 2004 at 9:59 PM

hmmmm, maybe i'm not understanding how to use INDIRECT then?

Don't want what's circled on the spreadsheet. And why are they dimmed?

D10 should equal 10, not 0. Notice I'm using the sum() in D10. It should read D5 as a value of 4, even tho it's displaying 'A'. That's all I want on the spreadsheet pretty much. Do I need to create my own function to do this? Unfortunately I'm unfamiliar with VBA and unfamilar with Visual Basic in general.

32

Oct 11, 2004 at 10:07 PM

The issue is that while you certainly can use either lookup (though vlookup is more up to date) or indirect, you cannot specify ranges for these functions. That is:
sum(indirect(b2:b4)) doesn't work. This largely defeats the purpose of using excel since you have to manually specify each cell.

183

Oct 11, 2004 at 10:08 PM

They're dim because they're hidden, and I'm in editing mode. If I was in viewing mode, they wouldn't show up. And for INDIRECT to work, you need to define what A, B, C, etc are, i.e, name some cells A, B, C, etc, and give them the proper values, and then hide them if you don't want to look at them.

Author

131

Oct 11, 2004 at 10:20 PM

ohhhh, i think i'm understanding it now! many thanks. I hid the column, but how do i get into editing mode to see the hidden columns.

183

Oct 11, 2004 at 10:24 PM

How should I know? I don't use Excel. Microsoft isn't known for making the most Linux friendly applications.

Author

131

Oct 11, 2004 at 10:27 PM

of course not, but however you did it in openoffice should be about the same as excel. everything you've mentioned so far about openoffice has been spot on with excel..

32

Oct 11, 2004 at 10:42 PM

you could just use this little VB function:

Function GPA(test As Range) As Double    For i = 1 To test.Count        Select Case test.Cells(i, 1)            Case Is = "A"                GPA = GPA + 4            Case Is = "B"                GPA = GPA + 3            Case Is = "C"                GPA = GPA + 2            Case Is = "D"                GPA = GPA + 1        End Select    Next i    GPA = GPA / test.CountEnd Function

effectively averages a range of cells substituting the appropriate value for GPA, not the most elegant solution, but it will work

183

Oct 11, 2004 at 10:45 PM

Okay. . . in OO they'll dissappear after the sheet is protected (Tools/Protect Document/...), or you print it. And to get into editing mode, well, either don't open it read only, or click that button between save and PDF. In my picture it's disabled because I hadn't saved it. If there's a shortcut for it, I don't know it. I doubt that translates back very easily to Excel.

Author

131

Oct 11, 2004 at 11:36 PM

Quote:
Original post by chowe6685
you could just use this little VB function:
*** Source Snippet Removed ***

effectively averages a range of cells substituting the appropriate value for GPA, not the most elegant solution, but it will work

That's awesome. Except that's not how I calculate my GPA in college. It works like a charm though. The formula for calculating my GPA is: (unit * grade)/total units

Now after messing around with that function, I can't seem to get it to work how I want it to work.

See how that's working? The D column the B column * C column rows individually. So D3 = 5 * 4 because A=4. And down the row. Then you add the numbers in the D column and add the numbers in the B column. Then divide the total of D11 by B11 to get the GPA. Unfortunately, my programming and math skills aren't good enough to do this. Is there a way to make a function that will calculate all of that and give the GPA with just being given the Units of the class and the grade of the class? Thanks so much for all your help. Rating ++ for Smart_Idiot and Chowe6685.

Could someone still please help me make that new function?

32

Oct 12, 2004 at 12:08 AM

this is not exactly robust

Function GPA(test As Range) As Double    Dim Units As Integer    Units = 0    For i = 1 To test.Count / 2        Units = Units + test.Cells(i, 1)        Select Case test.Cells(i, 2)            Case Is = "A"                GPA = GPA + 4 * test.Cells(i, 1)            Case Is = "B"                GPA = GPA + 3 * test.Cells(i, 1)            Case Is = "C"                GPA = GPA + 2 * test.Cells(i, 1)            Case Is = "D"                GPA = GPA + 1 * test.Cells(i, 1)        End Select    Next i    GPA = GPA / UnitsEnd Function

Author

131

Oct 12, 2004 at 12:16 AM

hmmmm, i can't get it to work. What range do I pass as the argument? Units column or Grades column? When I use the Grades column, I get the #VALUE! error. When I use the Units column, I get 0.

I really appreciate the help :) Lets seee if we can get it to work.

32

Oct 12, 2004 at 12:17 AM

both, select them all and make sure the units column is first

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.