Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
G'day All,
Is it possible to use variables in formulas without storing them in a cell? i.e. (a simplified example) VarA=SUMIF(A:A,Y:Y,X:X) VarB=SUMIF(A:A,Y:Y,W:W) IF(B:B<VarA,VarA,VarB) looks a lot better than IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W)) The formula I have is too long and complex therefor would be a nightmare to trouble shoot a year down the track. Also storing the variables in a cell seems sloppy to me. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
Have you thought about storing them as a named range?
http://www.cpearson.com/excel/named.htm Scroll down to the Naming Formulas section. -- HTH, Barb Reinhardt "build" wrote: G'day All, Is it possible to use variables in formulas without storing them in a cell? i.e. (a simplified example) VarA=SUMIF(A:A,Y:Y,X:X) VarB=SUMIF(A:A,Y:Y,W:W) IF(B:B<VarA,VarA,VarB) looks a lot better than IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W)) The formula I have is too long and complex therefor would be a nightmare to trouble shoot a year down the track. Also storing the variables in a cell seems sloppy to me. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
Thanks Barb, exactly what I want!
I should have looked at Chips site (www.cpearson.com) but it's been quite a while since I've used excel and it inexplicably slipped my mind. For anyone else reading this who is not familar with Chips site, I strongly recommend a visit it is an extremely useful place with some bloody clever stuff. Does Chip still post to these groups? What about Thomas? Another very clever bloke! cheers, build "Barb Reinhardt" wrote in message ... Have you thought about storing them as a named range? http://www.cpearson.com/excel/named.htm Scroll down to the Naming Formulas section. -- HTH, Barb Reinhardt "build" wrote: G'day All, Is it possible to use variables in formulas without storing them in a cell? i.e. (a simplified example) VarA=SUMIF(A:A,Y:Y,X:X) VarB=SUMIF(A:A,Y:Y,W:W) IF(B:B<VarA,VarA,VarB) looks a lot better than IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W)) The formula I have is too long and complex therefor would be a nightmare to trouble shoot a year down the track. Also storing the variables in a cell seems sloppy to me. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
Does Chip still post to these groups?
Indeed I do. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "build" wrote in message ... Thanks Barb, exactly what I want! I should have looked at Chips site (www.cpearson.com) but it's been quite a while since I've used excel and it inexplicably slipped my mind. For anyone else reading this who is not familar with Chips site, I strongly recommend a visit it is an extremely useful place with some bloody clever stuff. Does Chip still post to these groups? What about Thomas? Another very clever bloke! cheers, build "Barb Reinhardt" wrote in message ... Have you thought about storing them as a named range? http://www.cpearson.com/excel/named.htm Scroll down to the Naming Formulas section. -- HTH, Barb Reinhardt "build" wrote: G'day All, Is it possible to use variables in formulas without storing them in a cell? i.e. (a simplified example) VarA=SUMIF(A:A,Y:Y,X:X) VarB=SUMIF(A:A,Y:Y,W:W) IF(B:B<VarA,VarA,VarB) looks a lot better than IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W)) The formula I have is too long and complex therefor would be a nightmare to trouble shoot a year down the track. Also storing the variables in a cell seems sloppy to me. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
Thomas Ramel?
Still active in European groups. Mainly German groups. Gord Dibben MS Excel MVP On Thu, 11 Oct 2007 10:08:52 +1000, "build" wrote: Thanks Barb, exactly what I want! I should have looked at Chips site (www.cpearson.com) but it's been quite a while since I've used excel and it inexplicably slipped my mind. For anyone else reading this who is not familar with Chips site, I strongly recommend a visit it is an extremely useful place with some bloody clever stuff. Does Chip still post to these groups? What about Thomas? Another very clever bloke! cheers, build "Barb Reinhardt" wrote in message ... Have you thought about storing them as a named range? http://www.cpearson.com/excel/named.htm Scroll down to the Naming Formulas section. -- HTH, Barb Reinhardt "build" wrote: G'day All, Is it possible to use variables in formulas without storing them in a cell? i.e. (a simplified example) VarA=SUMIF(A:A,Y:Y,X:X) VarB=SUMIF(A:A,Y:Y,W:W) IF(B:B<VarA,VarA,VarB) looks a lot better than IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W)) The formula I have is too long and complex therefor would be a nightmare to trouble shoot a year down the track. Also storing the variables in a cell seems sloppy to me. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
LOL
G'day Chip, You must be grey or bald or both by now :-) I'm pure white and rather thin on top and in the top too. I guess you'd like a cent for every question you've answered on these groups, huh? My offer still stands to buy you a meal and a beer or 50 if your ever downunder in gods own country and that offer goes to Thomas if he's still around (is he?) From memory your west coast? if not are you near Indy? I get there once a year but never have much time to spare. cheers, build "Chip Pearson" wrote in message ... Does Chip still post to these groups? Indeed I do. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "build" wrote in message ... Thanks Barb, exactly what I want! I should have looked at Chips site (www.cpearson.com) but it's been quite a while since I've used excel and it inexplicably slipped my mind. For anyone else reading this who is not familar with Chips site, I strongly recommend a visit it is an extremely useful place with some bloody clever stuff. Does Chip still post to these groups? What about Thomas? Another very clever bloke! cheers, build "Barb Reinhardt" wrote in message ... Have you thought about storing them as a named range? http://www.cpearson.com/excel/named.htm Scroll down to the Naming Formulas section. -- HTH, Barb Reinhardt "build" wrote: G'day All, Is it possible to use variables in formulas without storing them in a cell? i.e. (a simplified example) VarA=SUMIF(A:A,Y:Y,X:X) VarB=SUMIF(A:A,Y:Y,W:W) IF(B:B<VarA,VarA,VarB) looks a lot better than IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W)) The formula I have is too long and complex therefor would be a nightmare to trouble shoot a year down the track. Also storing the variables in a cell seems sloppy to me. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
Damn, this is confusing me and I think it's the references absolute/relative
stuff etc? I have a function =SUMIF(AA:AA,A:A,Z:Z) ... works well when used in for example =IF(SUMIF(AA:AA,A:A,Z:Z)=1,10,IF(SUMIF(AA:AA,A:A,Z :Z)=2,5,0)) but when I name it. Qual_Pos =SUMIF($A$A:$AA,$A:A,$Z:$Z) it is changed to: =SUMIF('1-Australia'!$AA:$AA,'1-Australia'!$A:$A,'1-Australia'!$Z:$Z) when i try: =IF(Qual_Pos=1,10,IF(Qual_Pos=2,5,0)) it does not give the expected result. I've tried: =SUMIF(AA:AA,A:A,Z:Z) and other variations. What am I missing? Also I think I'd rather a relative reference as I'm still building the workbook and inserting and deleting columns. again Thanking you in anticipation, build "Barb Reinhardt" wrote in message ... Have you thought about storing them as a named range? http://www.cpearson.com/excel/named.htm Scroll down to the Naming Formulas section. -- HTH, Barb Reinhardt "build" wrote: G'day All, Is it possible to use variables in formulas without storing them in a cell? i.e. (a simplified example) VarA=SUMIF(A:A,Y:Y,X:X) VarB=SUMIF(A:A,Y:Y,W:W) IF(B:B<VarA,VarA,VarB) looks a lot better than IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W)) The formula I have is too long and complex therefor would be a nightmare to trouble shoot a year down the track. Also storing the variables in a cell seems sloppy to me. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
Ahhhhaaaaaaa
from http://www.cpearson.com/excel/named.htm "They do not depend on the cell from which the name is invoked. " ... Hmmm If they do not depend on the cell from which the name is invoked then is it possible to have a reference for a LOOKUP or SUMIF? Now I'm really confused. "build" wrote in message ... Damn, this is confusing me and I think it's the references absolute/relative stuff etc? I have a function =SUMIF(AA:AA,A:A,Z:Z) ... works well when used in for example =IF(SUMIF(AA:AA,A:A,Z:Z)=1,10,IF(SUMIF(AA:AA,A:A,Z :Z)=2,5,0)) but when I name it. Qual_Pos =SUMIF($A$A:$AA,$A:A,$Z:$Z) it is changed to: =SUMIF('1-Australia'!$AA:$AA,'1-Australia'!$A:$A,'1-Australia'!$Z:$Z) when i try: =IF(Qual_Pos=1,10,IF(Qual_Pos=2,5,0)) it does not give the expected result. I've tried: =SUMIF(AA:AA,A:A,Z:Z) and other variations. What am I missing? Also I think I'd rather a relative reference as I'm still building the workbook and inserting and deleting columns. again Thanking you in anticipation, build "Barb Reinhardt" wrote in message ... Have you thought about storing them as a named range? http://www.cpearson.com/excel/named.htm Scroll down to the Naming Formulas section. -- HTH, Barb Reinhardt "build" wrote: G'day All, Is it possible to use variables in formulas without storing them in a cell? i.e. (a simplified example) VarA=SUMIF(A:A,Y:Y,X:X) VarB=SUMIF(A:A,Y:Y,W:W) IF(B:B<VarA,VarA,VarB) looks a lot better than IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W)) The formula I have is too long and complex therefor would be a nightmare to trouble shoot a year down the track. Also storing the variables in a cell seems sloppy to me. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
OK finally got my head around it.
But I'm not going to try to confuse someone reading this with a clumsy explaination, they'll hopefully understand and interpret the solution to my problem which was: =SUMIF($AA:$AA,$A3,$Z:$Z) regards, build "build" wrote in message ... Ahhhhaaaaaaa from http://www.cpearson.com/excel/named.htm "They do not depend on the cell from which the name is invoked. " ... Hmmm If they do not depend on the cell from which the name is invoked then is it possible to have a reference for a LOOKUP or SUMIF? Now I'm really confused. "build" wrote in message ... Damn, this is confusing me and I think it's the references absolute/relative stuff etc? I have a function =SUMIF(AA:AA,A:A,Z:Z) ... works well when used in for example =IF(SUMIF(AA:AA,A:A,Z:Z)=1,10,IF(SUMIF(AA:AA,A:A,Z :Z)=2,5,0)) but when I name it. Qual_Pos =SUMIF($A$A:$AA,$A:A,$Z:$Z) it is changed to: =SUMIF('1-Australia'!$AA:$AA,'1-Australia'!$A:$A,'1-Australia'!$Z:$Z) when i try: =IF(Qual_Pos=1,10,IF(Qual_Pos=2,5,0)) it does not give the expected result. I've tried: =SUMIF(AA:AA,A:A,Z:Z) and other variations. What am I missing? Also I think I'd rather a relative reference as I'm still building the workbook and inserting and deleting columns. again Thanking you in anticipation, build "Barb Reinhardt" wrote in message ... Have you thought about storing them as a named range? http://www.cpearson.com/excel/named.htm Scroll down to the Naming Formulas section. -- HTH, Barb Reinhardt "build" wrote: G'day All, Is it possible to use variables in formulas without storing them in a cell? i.e. (a simplified example) VarA=SUMIF(A:A,Y:Y,X:X) VarB=SUMIF(A:A,Y:Y,W:W) IF(B:B<VarA,VarA,VarB) looks a lot better than IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W)) The formula I have is too long and complex therefor would be a nightmare to trouble shoot a year down the track. Also storing the variables in a cell seems sloppy to me. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variables in formulas
=SUMIF($AA:$AA,$A3,$Z:$Z) this is exactly the same formula I have used on an excel 2003 sheet for years. However it does not work when I open the same worksheet in excel 2007. It just returns 0. Cannot work out why. I've tried formatting the cells differently, using tables, names etc what am I doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What if < with multiple variables and formulas | Excel Discussion (Misc queries) | |||
multiple variables in sumproduct or if/then formulas | Excel Worksheet Functions | |||
How do I string formulas together in Excel to display variables | New Users to Excel | |||
Using formulas to source a value based on 2 variables | Excel Worksheet Functions | |||
How can I use variables in formulas in VB? | Excel Discussion (Misc queries) |