Try this:
=MROUND(K16*1.03,0.25)
Try this:
=MROUND(K16*1.03,0.25)
Welcome back. The formula repeats these --> █ n times where n is the number of cells in the row that are not empty. The repetition of these black rectangles are what creates the bar.
Just highlight the formula in post #9 and paste it into cell A2.
If you want to combine data in D4 and D6 into cell B6, you can use CONCATENATE like this: =CONCATENATE(D4,D6) or even easier, =D4&D6
Note that if you want to put a space in between the last name and address, you can do that like this:
=CONCATENATE(D4," ",D6)
or my preferred method, like this:
=D4&" "&D6
Put either of those formulas in cell B6.
I'm glad that you found a working solution.
In the future, uploading a small representative sample of your data along with the desired outcome of the formula will be the best way to help us help you.
It's difficult to provide a working formula without seeing a small sample of your data.
That being said, try this:
=SUMPRODUCT((MONTH(Bank[Date])=MONTH(A2))*(YEAR(Bank[Date])=YEAR(A2))*(Bank[Category]=B1)*Bank[Value])
Upon further inspection, this (in column A) works better:
=REPT("█",COUNTA(B2:O2))
You can keep the Conditional Formatting formulas the same or use these:
1) =COUNTA(B2:O2)=14 (Green font)
2) =COUNTA(B2:O2)<14 (Yellow font)
Here's another option if you want to change the color of the bars:
https://stackoverflow.com/questions/3063…-based-on-color
See attachment to see this used in your application.
A2 =REPT("█",10*COUNTA(B2:O2)/14)
Used 2 conditional formatting rules:
1) =COUNTA(B2:O2)/14=1 (Green font)
2) =COUNTA(B2:O2)/14<1 (Yellow font)
I don't think that there is much versatility with the data bars (changing color conditionally or disappearing completely).
One option, if you want to have a green check mark show up in the cell when the value reaches 100%, you can add another Conditional Formatting rule using icon sets.
Highlight A2:A333 > Conditional Formatting > Icon Sets > 3 Symbols
Now, with any of the cells in A2:A333 selected, Conditional Formatting > Manage Rules > Edit Rule > Check the "Show Icon Only" box > change the bottom two icons to "No Cell Icon" > change the first Value to 1 and the Type to Number > OK > OK
This will show the bars and a green check mark when the bar hits 100%.
You're welcome. Happy to help.
You only have to put the formula in the first cell (A2) and then drag it down as far as needed. The rows will automatically adjust in the formula as you drag it down the column.
Also, I would change Minimum and Maximum from Automatic to Number (0 and 1 respectively).
I created a small sample and attached it here.
As they said, in A2, use this formula: =COUNTA(B2:O2)/14
Copy that formula down column A.
Select A2:A333 > Conditional Formatting > Data Bars > Pick which one you want
Now, with any of the cells in A2:A333 selected, Conditional Formatting > Manage Rules > Edit Rule > Check the "Show Bar Only" box > OK > OK
I just joined looking to help but as mentioned, I noticed that there doesn't seem to be much activity here.
This is not the norm for this forum?
Another option, assuming the same locations shown from post #2, and that there are no duplicates, is this:
C3 =SUMIFS(N:N,L:L,A3,M:M,B3)
Make sure to format column C as dates.
You have a couple of options. Here's one:
Assuming that LIST1 is in A2:B5, and LIST2 is in L2:N5 (headers in row 2), try this in C3
=INDEX(N$3:N$5,MATCH(A3&B3,L$3:L$5&M$3:M$5,0)) Ctrl Shift Enter
One option is to use a helper column (column C here) to show duplicate products.
C2 =COUNTIF(A$2:A2,A2)
Then try this in E2:
=COUNTIFS(B:B,D2,C:C,1)