Formulas Returning Wrong Values

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,870
Will someone please tell me why the formulas in G:K are returning wrong values. They are suppose to count how many of the values in B:F repeat. I think it may be a format problem, but can't find it.








Book1
ABCDEFGHIJK
1412345
1512/30/16031417253611111
1612/29/16030513233611111
1712/28/16071422343511111
1812/27/16050913151711111
1912/26/16132630333611111
LAST DIGIT (2)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Well, without seeing the formulae, I'm not sure we can tell! What are your expected results?
 
Upvote 0
Well, without seeing the formulae, I'm not sure we can tell! What are your expected results?

The formulas are not showing for some reason, don't understand why not.

starting in G15
=SUMPRODUCT(IF(COUNTIF(OFFSET($B15,1,0,G$14,5),$B15:$F15)<COUNTIF($B15:$F15,$B15:$F15),COUNTIF(OFFSET($B15,1,0,G$14,5),$B15:$F15)/COUNTIF($B15:$F15,$B15:$F15),1))

1 2 3 4 5
12/30/16 03 14 17 25 36 1 1 1 1 1
12/29/16 03 05 13 23 36 1 1 1 1 1
12/28/16 07 14 22 34 35 1 1 1 1 1
12/27/16 05 09 13 15 17 1 1 1 1 1
12/26/16 13 26 30 33 36 1 1 1 1 1
 
Upvote 0
Thanks. What are your expected results? The formula seems incomplete - is there something missing at the end?
 
Last edited:
Upvote 0
@AliGW the complete formula the OP posted is...

=SUMPRODUCT(IF(COUNTIF(OFFSET($B15,1,0,G$14,5),$B15:$F15)<COUNTIF($B15:$F15,$B15:$F15),COUNTIF(OFFSET($B15,1,0,G$14,5),$B15:$F15)/COUNTIF($B15:$F15,$B15:$F15),1))
 
Last edited:
Upvote 0
I installed the HTML MAKER as an add-in, checked default and show formulas,is that right or wrong?
 
Upvote 0
Thanks, Mark, but how do you know that? :confused:

Click reply with quote and you can normally see the full formula when Vbulletin is treating the < as a Html tag.
 
Upvote 0
Oooh, nice trick - thanks for that! :)

Doesn't work here in Firefox: I just get an empty message box. :(
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top