## Hot answers tagged worksheet-function

3

This is just how EXCEL behaves. You can see the behaviour quite simple by:
make sure Cell A1 is blank/empty.
put this formula into B1: =A1
note how B1 shows a value of "0".
put this formula into C1: =IF(ISBLANK(A1),"",A1)
You can use that similar ISBLANK check in your original query.
=IF(K14<>"",VLOOKUP(K14,Sheet132!$A$2:$E$333,3,FALSE),if(...

2

I think the formula =RIGHT(A2,1) will do what you want. If there might be a space at the end of the text, you could use =RIGHT(TRIM(A2,1)) to ignore the space.
If you want something even more understandable, format your data as a table. Select all your data, go to Home→Styles→Format As Table, and make sure the "My Table has Headers" checkbox is selected. ...

1

You need to bracket the dates with greater than and less than:
=SUMIFS(Spending!C:C,Spending!D:D,C1,Spending!A:A,">=" & DATE(YEAR(D1),MONTH(D1),1),Spending!A:A,"<=" & EOMONTH(D1,0))
For reference Spending sheet:

1

One question at a time.
For the first one, try Countif() like this for a count of the value.
=COUNTIF(A:A,"*"&A1&"*")
If you want to mark the rows where the value appears, try in B1 and copy down
=IF(ISNUMBER(SEARCH($A$1,A7)),"found","")
For the other issue, please open a new question and explain what you want to achieve.

1

you could use the formula:
=MIN(IF(COUNTIF(D4:AB4,">0")<3,C4,IF(COUNTIF(D4:AB4,">0")<=5,SUM(IF(D4:AB4>0,D4:AB4-36))/COUNTIF(D4:AB4,">0"),SUM(IF(COLUMN(D4:AB4)=LARGE(IF(D4:AB4>0,COLUMN(D4:AB4)),ROW($1:$5)),D4:AB4-36)/5))*0.9),20)
This is an array formula and must be confirmed with ctrl+shift+enter!
This fomula simply can be copied ...

1

This can be accomplished with conditional formatting on the TRUE cells in latest_action. All these formulas are created within a table, see this link if you're not sure how to make one, and this link for how I did the cell references.
My formulas in each column are:
count: =COUNTIF([membership_number],[@[membership_number]])
sub_rank: =SUMPRODUCT(([...

Only top voted, non community-wiki answers of a minimum length are eligible