Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
why is there a { in my formula and why wont it work?
Evening,
I've taken over a membership spreadsheet for a club. Part of the spreadsheet ("report" sheet) produces a break down of the membership dependant on where they work, splitting it further to the type of work. I've noticed that the "report" has missed a couple of trades, so I copied the formula and changed the text to check for. The formula is as follows: =SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517= "assistant",1,0))) I want to sum the people that work in the shop but are cleaners, so I changed assistant to cleaners, and although there are 3 in the list the formula displays a zero. If I view the formula by pressing the "insert function" button it says it should return a 3 but still displays a zero when I close insert function page. One thing I have noticed is that all the formulas in the "report" sheet have { at the start (before the = sign) and } at the end. When I put these on the copied formula it displays the whole formula in the cell rather than the result of the formula. The spreadsheet is something I've inherited so any help would be appreciated. Many thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
why is there a { in my formula and why wont it work?
It's an array formula and it needs to be entered with Ctrl Shift Enter.
Click the formula in the Formula Bar and press all three keys at the same time. The result should look like this, {=SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517 ="assistant",1,0)))} Array formulas can only be entered this way, you can't put the {}'s in yourself, Regards, Alan. "Gorgsey" wrote in message ... Evening, I've taken over a membership spreadsheet for a club. Part of the spreadsheet ("report" sheet) produces a break down of the membership dependant on where they work, splitting it further to the type of work. I've noticed that the "report" has missed a couple of trades, so I copied the formula and changed the text to check for. The formula is as follows: =SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517= "assistant",1,0))) I want to sum the people that work in the shop but are cleaners, so I changed assistant to cleaners, and although there are 3 in the list the formula displays a zero. If I view the formula by pressing the "insert function" button it says it should return a 3 but still displays a zero when I close insert function page. One thing I have noticed is that all the formulas in the "report" sheet have { at the start (before the = sign) and } at the end. When I put these on the copied formula it displays the whole formula in the cell rather than the result of the formula. The spreadsheet is something I've inherited so any help would be appreciated. Many thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
why is there a { in my formula and why wont it work?
If I have a choice between using a formula that needs to be array entered
(ctrl-shift-enter, like Alan explained) and a formula that just needs to be entered normally, I'll choose the normal enter. =sumproduct(--(list!$k$3:$k$517="shop"),--(list!$f$3:$f$517="assistant")) (or if you're using xl2007, look at =countifs() in excel's help.) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Gorgsey wrote: Evening, I've taken over a membership spreadsheet for a club. Part of the spreadsheet ("report" sheet) produces a break down of the membership dependant on where they work, splitting it further to the type of work. I've noticed that the "report" has missed a couple of trades, so I copied the formula and changed the text to check for. The formula is as follows: =SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517= "assistant",1,0))) I want to sum the people that work in the shop but are cleaners, so I changed assistant to cleaners, and although there are 3 in the list the formula displays a zero. If I view the formula by pressing the "insert function" button it says it should return a 3 but still displays a zero when I close insert function page. One thing I have noticed is that all the formulas in the "report" sheet have { at the start (before the = sign) and } at the end. When I put these on the copied formula it displays the whole formula in the cell rather than the result of the formula. The spreadsheet is something I've inherited so any help would be appreciated. Many thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
why is there a { in my formula and why wont it work?
hi,
i agree with dave meaning there is more than one way to get from here to there and personally, i don't like array formulas either. but sometime they do come in handy. rare by my experiece. but i also agree that Alan's responce may not really cover array formulas. (no offence allen) see this site for a more detailed explination of array formulas. this site may not cover all, but it may provide more understanding of array formulas. http://www.cpearson.com/excel/ArrayFormulas.aspx regards FSt1 "Dave Peterson" wrote: If I have a choice between using a formula that needs to be array entered (ctrl-shift-enter, like Alan explained) and a formula that just needs to be entered normally, I'll choose the normal enter. =sumproduct(--(list!$k$3:$k$517="shop"),--(list!$f$3:$f$517="assistant")) (or if you're using xl2007, look at =countifs() in excel's help.) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Gorgsey wrote: Evening, I've taken over a membership spreadsheet for a club. Part of the spreadsheet ("report" sheet) produces a break down of the membership dependant on where they work, splitting it further to the type of work. I've noticed that the "report" has missed a couple of trades, so I copied the formula and changed the text to check for. The formula is as follows: =SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517= "assistant",1,0))) I want to sum the people that work in the shop but are cleaners, so I changed assistant to cleaners, and although there are 3 in the list the formula displays a zero. If I view the formula by pressing the "insert function" button it says it should return a 3 but still displays a zero when I close insert function page. One thing I have noticed is that all the formulas in the "report" sheet have { at the start (before the = sign) and } at the end. When I put these on the copied formula it displays the whole formula in the cell rather than the result of the formula. The spreadsheet is something I've inherited so any help would be appreciated. Many thanks. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
why is there a { in my formula and why wont it work?
'but i also agree that Alan's responce may not really cover array formulas.
(no offence allen)' No offence taken, but agree with who? Regards, Alan "FSt1" wrote in message ... hi, i agree with dave meaning there is more than one way to get from here to there and personally, i don't like array formulas either. but sometime they do come in handy. rare by my experiece. but i also agree that Alan's responce may not really cover array formulas. (no offence allen) see this site for a more detailed explination of array formulas. this site may not cover all, but it may provide more understanding of array formulas. http://www.cpearson.com/excel/ArrayFormulas.aspx regards FSt1 "Dave Peterson" wrote: If I have a choice between using a formula that needs to be array entered (ctrl-shift-enter, like Alan explained) and a formula that just needs to be entered normally, I'll choose the normal enter. =sumproduct(--(list!$k$3:$k$517="shop"),--(list!$f$3:$f$517="assistant")) (or if you're using xl2007, look at =countifs() in excel's help.) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Gorgsey wrote: Evening, I've taken over a membership spreadsheet for a club. Part of the spreadsheet ("report" sheet) produces a break down of the membership dependant on where they work, splitting it further to the type of work. I've noticed that the "report" has missed a couple of trades, so I copied the formula and changed the text to check for. The formula is as follows: =SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517= "assistant",1,0))) I want to sum the people that work in the shop but are cleaners, so I changed assistant to cleaners, and although there are 3 in the list the formula displays a zero. If I view the formula by pressing the "insert function" button it says it should return a 3 but still displays a zero when I close insert function page. One thing I have noticed is that all the formulas in the "report" sheet have { at the start (before the = sign) and } at the end. When I put these on the copied formula it displays the whole formula in the cell rather than the result of the formula. The spreadsheet is something I've inherited so any help would be appreciated. Many thanks. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
why is there a { in my formula and why wont it work?
When you see those squiggly brackets { } around a formula it means that that
formula is an array formula. Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key. When done properly Excel will enclose the formula in the squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo. -- Biff Microsoft Excel MVP "Gorgsey" wrote in message ... Evening, I've taken over a membership spreadsheet for a club. Part of the spreadsheet ("report" sheet) produces a break down of the membership dependant on where they work, splitting it further to the type of work. I've noticed that the "report" has missed a couple of trades, so I copied the formula and changed the text to check for. The formula is as follows: =SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517= "assistant",1,0))) I want to sum the people that work in the shop but are cleaners, so I changed assistant to cleaners, and although there are 3 in the list the formula displays a zero. If I view the formula by pressing the "insert function" button it says it should return a 3 but still displays a zero when I close insert function page. One thing I have noticed is that all the formulas in the "report" sheet have { at the start (before the = sign) and } at the end. When I put these on the copied formula it displays the whole formula in the cell rather than the result of the formula. The spreadsheet is something I've inherited so any help would be appreciated. Many thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
why is there a { in my formula and why wont it work?
sorry to be confusing. i may have been agreeing with me or perhaps i chose a
bad way to express myself.. you did explain how to enter an array formula and were very correct but the OP what wondering just what these brackets were and what they were for(althought not stated directly) . you, dave and T.Valko(who may have posted after i did) didn't seem to address the what and why about an array formula. so i directed the op to a sited that might give some understanding about array formulas and not just how to enter them. as stated, i don't like them but do admit that they can be handy at time. again, sorry for any confusion. regards FSt1 "Alan" wrote: 'but i also agree that Alan's responce may not really cover array formulas. (no offence allen)' No offence taken, but agree with who? Regards, Alan "FSt1" wrote in message ... hi, i agree with dave meaning there is more than one way to get from here to there and personally, i don't like array formulas either. but sometime they do come in handy. rare by my experiece. but i also agree that Alan's responce may not really cover array formulas. (no offence allen) see this site for a more detailed explination of array formulas. this site may not cover all, but it may provide more understanding of array formulas. http://www.cpearson.com/excel/ArrayFormulas.aspx regards FSt1 "Dave Peterson" wrote: If I have a choice between using a formula that needs to be array entered (ctrl-shift-enter, like Alan explained) and a formula that just needs to be entered normally, I'll choose the normal enter. =sumproduct(--(list!$k$3:$k$517="shop"),--(list!$f$3:$f$517="assistant")) (or if you're using xl2007, look at =countifs() in excel's help.) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Gorgsey wrote: Evening, I've taken over a membership spreadsheet for a club. Part of the spreadsheet ("report" sheet) produces a break down of the membership dependant on where they work, splitting it further to the type of work. I've noticed that the "report" has missed a couple of trades, so I copied the formula and changed the text to check for. The formula is as follows: =SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517= "assistant",1,0))) I want to sum the people that work in the shop but are cleaners, so I changed assistant to cleaners, and although there are 3 in the list the formula displays a zero. If I view the formula by pressing the "insert function" button it says it should return a 3 but still displays a zero when I close insert function page. One thing I have noticed is that all the formulas in the "report" sheet have { at the start (before the = sign) and } at the end. When I put these on the copied formula it displays the whole formula in the cell rather than the result of the formula. The spreadsheet is something I've inherited so any help would be appreciated. Many thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't figure out why Macro wont work???? | Excel Worksheet Functions | |||
cntrl+n wont work | Excel Discussion (Misc queries) | |||
IF sum is GreaterThan 0... why wont this work!? | Excel Worksheet Functions | |||
Why wont this simple formula work | Excel Discussion (Misc queries) | |||
Control + C wont work | Excel Discussion (Misc queries) |