DOLLARFR formula

**cricket_stoner** · 03-30-2010, 11:45 PM

G'day,
A guru around here helped me simplify a formula that counts cricket overs. I've just noticed that it sometimes displays incorrectly. In the example attached, it shows 315.6, which should be 316. (It's like showing $5.100 instead of $6, I guess.) I've fiddled around with the overs and tried to work out a pattern to the error, but it seems to be random... Sometimes as you enter whole overs with no decimals, the total changes between a whole number and something-point-six. (For those who are wondering, there are six balls in a cricket over... five balls = 0.5, six balls = 1.0)

Does anyone know why this error appears some times and not others? Can anyone propose a fix? I had a more complex INT/MOD formula before, I can revert back to that if need be, but I'm curious.

Thanks in advance.

**shg** · 03-30-2010, 11:59 PM

Unfortunately, DOLLARDE is not array-compatible, which would make it easy. So,

=DOLLARFR(SUMPRODUCT(INT($K$5:$K$36) + ROUND(MOD($K$5:$K$36, 1) / 0.6, 0) ), 6)

**JeanRage** · 03-31-2010, 12:00 AM

Hi,

I guess you meant DOLLARFR ... for converting fractions ...
see explanation
http://www.bettersolutions.com/excel...R810212321.htm

HTH

**shg** · 03-31-2010, 12:03 AM

No, I meant DOLLARDE, which would allow you to do this:

=DOLLARFR(SUMPRODUCT(DOLLARDE($K$5:$K$36, 6) ), 6)

EDIT: Jean, if your comment was directed to the OP's title, my apologies. I changed it a moment ago.

**JeanRage** · 03-31-2010, 12:16 AM

Sorry shg ...

Indeed, my comment was directed to the OP ...
FR and DE are not immediately obvious ... and he was adding more confusion with PR ...

Cheers

**cricket_stoner** · 03-31-2010, 12:45 AM

Yeah sorry, I must have screwed up the title. That's how good I am with DOLLAR functions... haha.

Thanks for the fix, shg.

EDIT - I spoke too soon. It now always shows a whole number. Actually, the addition is all messed up now too. Cricket overs are counted in base 6. 0.1, 0.2, 0.3, 0.4, 0.5, 1.0, 1.1, 1.2 etc. So 0.3 + 0.3 = 1.0. The other formula got the right result but displayed .6 at the end sometimes instead of the next whole number. I tried changing the ROUND decimal places to 1 but that didn't help. Any ideas?

**DonkeyOte** · 03-31-2010, 03:17 AM

Perhaps:

=DOLLARFR(ROUND(SUMPRODUCT(INT($K$5:$K$36)+(MOD($K$5:$K$36,1)/0.6)),15),6)

On an aside, you mentioned on another thread the possible issue using ATP reliant functions for those using Viewer for the 2007 file, it might make sense to use the ATP-free alternatives ?
(Re: ATP alternative(s): I think you have already per your other file/thread).

**cricket_stoner** · 03-31-2010, 10:31 AM

Thanks DonkeyOte. Works perfectly as expected.

Yeah, perhaps it would be best to revert back to the ATP-free versions. Your DOLLAR formulae are more succinct but, as you said, it can be done without them. I had to revert back to my longer, older formulae in a couple of other areas too, mainly because I needed some zeros to show and they were all being caught by the custom format.

Anyway, thanks again.

**DonkeyOte** · 03-31-2010, 11:09 AM

Originally Posted by cricket_stoner

I had to revert back to my longer, older formulae in a couple of other areas too, mainly because I needed some zeros to show and they were all being caught by the custom format.

No need to revert the formulae simply adjust the custom format such that 0 is displayed rather than masked.
(try to maintain consistent data type wherever possible)

For an overview of custom formatting see:

http://pubs.logicalexpressions.com/p...cle.asp?ID=414
http://www.ozgrid.com/Excel/CustomFormats.htm
http://simoncpage.co.uk/blog/2008/09...er-formatting/

**cricket_stoner** · 03-31-2010, 02:00 PM

The problem with some cells are that whether they show the 0 or not is reliant on other cells. There were other things too, like some averages or rates that worked out to be zero were hidden instead of displayed. I couldn't think of a way to do that with formatting so I reverted to my old error-checking ways in some instances. I know it's not ideal but I've tested it thoroughly and it works as desired now. Actually, I think one of the best bowling formulae was producing an error if the bowler hadn't taken a wicket. I'll have to check that... I might be posting one more question then.

I've replaced the DOLLAR formulae with the old INT/MOD ones and saved the workbook as .xls. I want people who don't have Excel 2007 to be able to read them without installing anything. Cheers Donkey!