Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.14 seconds.

  1. 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. 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. 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. 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. 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. 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. 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. 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. 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. Replies
    2
    Views
    85

    Re: Max unless blank

    =if(count(d1:d100)=0, "", max(d1:d100))
  11. 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. 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. 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. 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. 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. 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. 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. 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...
  19. 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...
  20. 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. 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. 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. 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. 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. 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 2 3 4