# Search:

Type: Posts; User: joeu2004; Keyword(s):

1. ## Thread: generate a random number to enter in a cell

by joeu2004
Replies
16
Views
252

### Re: generate a random number to enter in a cell

[I wrote: ``there is no WorksheetFunction.Rand``]

From the VBA help page: ``Using square brackets (for example, "[A1:C5]") is identical to calling the Evaluate method with a string...
2. ## Thread: generate a random number to enter in a cell

by joeu2004
Replies
16
Views
252

### Re: generate a random number to enter in a cell

shg explained: starting in Excel 2010, Excel and WorksheetFunction.RandBetween use a better pseudorandom generator number algorithm. Here, "better" means: longer cycle and better distribution. ...
3. ## Thread: generate a random number to enter in a cell

by joeu2004
Replies
16
Views
252

### Re: generate a random number to enter in a cell

Actually, it is an "infinite times" faster, since there is no WorksheetFunction.Rand -- at least, not in Excel 2010. ;)

On my computer (YMMV), Worksheetfunction.RandBetween(lo,hi) is 8 to 16 times...
4. ## Thread: generate a random number to enter in a cell

by joeu2004
Replies
16
Views
252

### Re: generate a random number to enter in a cell

Simply:

ActiveCell = WorksheetFunction.RandBetween(Range("I9"), Range("I11"))

Although, if you are "looping through a range", there is no need to continuously change ActiveCell.

Dim r As...
5. ## Thread: How to Convert an Exponential Trendline Equation to a Formula

by joeu2004
Replies
9
Views
213

### Re: How to Convert an Exponential Trendline Equation to a Formula

I forgot to note that the red line is the trendline in both charts.

Note that it looks almost linear. In fact, the linear trendline has almost the same R^2, a measure of fit. And that would have...
6. ## Thread: How to Convert an Exponential Trendline Equation to a Formula

by joeu2004
Replies
9
Views
213

### Re: How to Convert an Exponential Trendline Equation to a Formula

First, my bad! I mixed up your columns: I thought column A has x-values, and column B has y-values.

That is more common, since the Insert Chart feature works best that way.

Honestly, I...
7. ## Thread: How to Convert an Exponential Trendline Equation to a Formula

by joeu2004
Replies
9
Views
213

### Re: How to Convert an Exponential Trendline Equation to a Formula

First, be sure that you are using an XY Scatter chart (perhaps with line subtype), not a Line chart. The trendline of a Line chart can be misleading because it always uses 1, 2, 3,... for the...
8. ## Thread: Alternative to Array Formula

by joeu2004
Replies
3
Views
135

### Re: Alternative to Array Formula

Pressing CSE per se is not the problem. It simply tells Excel that an otherwise ambiguous formula should be treated as a multi-valued (array) formula, not "implicit intersection" that returns a...
9. ## Thread: Alternative to Array Formula

by joeu2004
Replies
3
Views
135

### Re: Alternative to Array Formula

I don't think there is a non-array formula alternative. But the problem might not be array formulas per se, but your implementation of them.

At a minimum, I believe the following is more...
10. ## Thread: Max unless blank

by joeu2004
Replies
2
Views
85

### Re: Max unless blank

=if(count(d1:d100)=0, "", max(d1:d100))
11. ## Thread: Future employee salary/rasies

by joeu2004
Replies
2
Views
104

### Re: Future employee salary/rasies

=FV(3%, 5, -370000) / 5

To understand why, consider your manual calculation:

(370000 + 370000*(1+3%) + 370000*(1+3%)^2 + 370000*(1+3%)^3 + 370000*(1+3%)^4) / 5

The numerator is the same...
12. ## Thread: #N/A error with Lookup and Min functions

by joeu2004
Replies
5
Views
136

### Re: #N/A error with Lookup and Min functions

Sorry: I misread your question. As I wrote: ``LOOKUP(MAX...) might [work] only by coincidence``. LOOKUP(MIN...) might work by coincidence, as well.

LOOKUP does a binary search. Go to the...
13. ## Thread: Conditional XIRR with multiple columns for dates / cash flows

by joeu2004
Replies
2
Views
84

### Re: Conditional XIRR with multiple columns for dates / cash flows

Caveat: I use comma to separate parameters. You would use semicolons.

If we did it manually, we might array-enter (press ctrl+shift+enter instead of just Enter) the following formula:
...
14. ## Thread: #N/A error with Lookup and Min functions

by joeu2004
Replies
5
Views
136

### Re: #N/A error with Lookup and Min functions

No. I wrote: ``Be sure that E7:E1000 is in ascending order``.

Did the INDEX/MATCH formula work for you?
15. ## Thread: Tax Calculation formula

by joeu2004
Replies
7
Views
173

### Re: Tax Calculation formula

No. I think you are just overthinking it. There are two separate taxes: 1% up to \$50 for the county; and 6% for the state. So:

=MIN(50, (E8+E11)*1%) + (E8+E11)*6%

And again, you probably...
16. ## Thread: #N/A error with Lookup and Min functions

by joeu2004
Replies
5
Views
136

### Re: #N/A error with Lookup and Min functions

It is okay for there to be more than one match; just understand that you cannot predict which one LOOKUP will choose.

Be sure that E7:E1000 is in ascending order. Otherwise, do the following:
...
17. ## Thread: Tax Calculation formula

by joeu2004
Replies
7
Views
173

### Re: Tax Calculation formula

The formula does the calculation that you did in your example, to wit: \$50 plus 6% of \$5000.

But I never understood what you mean by ``I need it to retain the amount of the 1% for the first 5000...
18. ## Thread: Tax Calculation formula

by joeu2004
Replies
7
Views
173

### Re: Tax Calculation formula

=50 + MIN(300,6%*(E8+E11)) + MAX(0, 6%*((E8+E11)-5000))
or
=50 + MIN(300,6%*(E8+E11)) + MAX(0, 6%*(E8+E11)-300))

The two formulas are algebraically equivalent.

You might want to explicitly...

by joeu2004
Replies
4
Views
92

### Re: Calculating time

I meant: directly reference. We can always provide a formula. Consider using two cells: the visible one with DanteAmor's formula, and another cell (hidden?) with your original calculation formatted...

by joeu2004
Replies
4
Views
92

### Re: Calculating time

And just embellishing DanteAmor's response, don't forget that you can use the TEXT format "hh:mm" and right horizontal cell format so that the "negative time" lines up with normally-calculated...
21. ## Thread: Overflow - why?

by joeu2004
Replies
5
Views
146

### Re: Overflow - why?

You're welcome. But this is issue is not specific to multiplication. And the data type of any arithmetic operation is determined in a left-to-right evaluation, subject to operator precedence and...
22. ## Thread: Date Calculations not working

by joeu2004
Replies
9
Views
237

### Re: Date Calculations not working

Screenshots are almost useless for troubleshooting problems of this nature, since the problem might be an invisible extraneous character (or errant blank space) that causes Excel not to recognize the...
23. ## Thread: Date Calculations not working

by joeu2004
Replies
9
Views
237

### Re: Date Calculations not working

As I wrote before: ``The real question is: does Excel recognize the cell value as a date? [....] likely reason [why not]: the data entry contains extraneous characters``.

Yes, VALUE is more...
24. ## Thread: Date Calculations not working

by joeu2004
Replies
9
Views
237

### Re: Date Calculations not working

Not sure what you mean.

I meant that "1/1/2019"+14 calculates the date 1/15/2019 just fine (if dates are MDY), without any explicit conversion ("coercion"?). The cell format might need to be...
25. ## Thread: Date Calculations not working

by joeu2004
Replies
9
Views
237

### Re: Date Calculations not working

"Text that looks like a date" can be used in arithmetic expressions just fine.

The real question is: does Excel recognize the cell value as a date?

First, yes, does ISNUMBER(D8) return TRUE? ...
Results 1 to 25 of 150
Page 1 of 6 1