Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    need schizophrenic formula [work both directions]

    I have 2 columns C & D that are related thru a formula:
    C = A1 times D, where D is the variable and is entered to determine C
    If D[2] = 30%, then C[2] = $30 and if D[3] = 60%, then C[3] = $60.
    Great if D [%] is always the variable to be entered. But what if I would like to also go in the other direction and sometimes enter C to determine D? Restated, how can I be able to enter either C or D to calculate the related value, D or C?
    It appears this cannot be done with formulas in the cells as they may be overwritten if entering a value in the cell.

    See attached sample.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    see attached file.
    This uses event trapping to achieve your request.
    Enter any value in any row in column C or D to see the result.

    zeddy
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Skipro

    ..I imagine your original request was to find a technique, rather than solve that particular simple example.

    I have added a new sample spreadsheet which extends the technique to show a more 'realistic' example.

    It shows five columns for
    Cost Price, Product Description, Selling Price, Margin%, Markup Amount

    The event trapping allows you to make changes in these columns, while keeping the relationships valid.
    For example,
    Changing the Cost Price in col [A] will cause automatic recalc of the Selling Price and Markup Amount, based on keeping the Margin% in col [D]

    Changing a product description in col [B] has no effect

    Changing the Selling Price in col [C] will cause automatic recalc of Margin% and Markup Amount, based on keeping the Cost price in col [A]

    Changing the Margin% in col [D] will cause automatic recalc of Selling Price in col [C] and Markup Amount in col [E], based on Cost Price in col [A]

    Changing the Markup Amount in col [E] will cause automatic recalc of Selling Price in col [C] and Margin% in col [D], based on Cost Price in col [A]

    Also, changes detected in rows below 20 and beyond col [E] are ignored. (this can be adjusted easily in the sheet code to set desired ranges)

    Please let me know if this helps.

    zeddy
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    zeddy,
    Thanks, works as asked, but can you add these additional functions to accept a changing A1, as A1 is a moving target:
    1) Automatically recalculates C & D with changing A1.
    2) If A1=0, an "iserror" type function to return a text in C & D, such as "no entry", not an "div by zero" error, incorrect value or "freeze".

    Now if A1=0 and I change D, C is blank and I can do additional calculations, if I change D, I get a "div by 0" error [as expected] and no further calculations will occur.

    My actual worksheet is more involved but I think I can modify the resulting changes to my needs.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Skipro

    I was hoping my second sample might assist you.
    Try this attached version.
    It works for rows up to 20 (you can change this).

    You can type any value in col [C]
    You can enter a %percent value in col [D]
    You can change the value in [A1]

    If you delete [A1] (or enter text in cell [A1]), text will be displayed in C and D.
    If you subsequently enter a value in [A1], the text displayed in D will be reset to show 100%.

    see if this is nearer what you want.
    If you need further explanations, please let me know.

    zeddy
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    zeddy,
    You were correct in your second reply that the second sample [rz1-SKIPRO-sample-2.xlsm] you presented more closely addressed my needs. I did notice, appreciate and use your second sample and was able to modify it to my spreadsheet. Thank you for going that extra "mile" as it made it possible for me to incorporate it into my sheet. I submit simple samples to make the replies easier and simpler. I then attempt to modify the replies to my specific needs. All went according to plan, thanks to your "added step", but I am unable to modify your last reply to my sheet. Sorry for the subsequent redundancy.
    Attached is a sample sheet which more closely represents my sheet.
    Columns F & G are the columns in question, not C & D as in the original samples. F & G are derived from C & D, not A1 as in original sample.
    F4=(C4/D4)*G4)
    G4=(F4*D4)/C4
    C is the base number, but will change, and D will be derived from C, D=C/SUM(C$11). If C=0, then D=0
    F & G are working/variable numbers.

    My actual sheet originally incorporated an Iserror to get around a "0" in C & D.

    I am running into difficulties adapting my sheet with your last reply [rz1-SKIPRO-sample-3.xlsm] because I have the ranges C:C and D"colon"D not a single cell (A1) and ranges F:F and G:G which are derived from C:C and D"colon"D. How do I adapt your last script to this sheet using these ranges.
    I use D"colon"D because using a ":" with 2 D's creates an emoticon.

    Attached sample:
    Column F & G are the columns that have to calculate with [divide by] the "0" in C & D.
    F=(C/D)*G) and G=(F*D)/C.

    Also I had a script to force uppercase with M:M, but when I added your 1st script (modified) I ended up with 2 procedures with the same name, "Private Sub Worksheet_Change" and an error. (Note: In this sample, the first "Private Sub Worksheet_Change" [uppercase script] has been commented out to let your script run.)
    2) How do I combine the 2 procedures to resolve this error?

    Thank you.
    Attached Files Attached Files
    Last edited by skipro; 2015-02-11 at 21:14.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Skipro

    Thank you for thanking me.
    I realise it is hard to sometimes give simple examples, and I understand your reasons.
    The great thing about this lounge is that we can take it a step at a time.
    I like that. I like it when someone says "that's great, but can we now do this as well.."
    It gives plenty of opportunities for others to chip in, and you get to see different ways of doing things.
    And we all learn as we go on.

    Now, here's some info:
    I too got caught out with the colon emoticons, untill RetiredGeek told me how to do it -
    You type [ c o d e ] without spaces, then your formula or whatever with the colons etc and then end with [ / c o d e ]
    (without the spaces). Like this
    Code:
     D:D
    Now, I have attached an updated sample file.
    To deal with the column [M] issue, we just include this in our Select Case section.
    We can deal with any columns we like. So just remove your previous code, and include the test, as per the attached file example.
    Instead of trying to remember what the column number is for any particular column, I use something like [m1].Column to return the number.
    It's a lot easier e.g. [AQ1].Column (what's that column number ???) and is easier to follow in the code.

    Now, in your example file, if we change a %value in G, the total% in cell [G11] could be different from 100%.
    You could manually adjust the other G entries to 'fix' this.
    Alternatively, you could have this done automatically.
    How? Well, we could take the 'difference in percent', and either 'distribute' this difference equally to the other G values.
    Or, we could 'distribute' this difference on a 'proportional' basis to the other G values.
    Just thought you might like to know that.

    zeddy
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    zeddy.
    Again, thanks, especially for explanations so I can decipher and learn.
    I noticed you changed your approach in v.4 in that you replaced the formula in "F" in v.4 when it was overwritten, where as you did the calculation for "F" in the script in v.3. Any reason, plus/minuses?

    How can this be accomplished? [in previous request]
    If C[D]=0, incorporate an "iserror" type function to return a text in F & G, such as "no entry", not an "div by zero" error and eliminate any freezes or failure to update."
    In v.4, when C=0, F gives a div/0 and then continues functioning after replacing "0" with a number > "0" in C. G gives a debug error and then ceases to update after "0" is replaced with number > 0.
    I tried adding an ISERROR to the formula in F in your script in v.4, got no where.
    I tried scripting it and could get a text return in F but then other things failed to work.
    Last edited by skipro; 2015-02-13 at 23:17.

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Skipro

    Well spotted. That was deliberate, to show you another way.
    Given the choice, I think it is better to show formulas rather than just the values, because it shows the data relationships.
    That does not mean you cannot 'overwrite' the formula.

    If you want to specify the value you would like, you overwrite the formula with the value you want, then have vba make the other cells 'comply', and then have vba put the formula back.
    The formula will now give the same value as you wanted (since your vba made the other values 'comply').

    There are occasions when you can't do this.
    For example, suppose you have these columns:
    A=Unit Cost, B=Unit Selling Price, C=Units Sold, D=Total Sales Amt, E=Total Profit
    You could then 'overwrite' the formula in E (to give a particular 'desired' profit value)
    Working backwards, you have to then decide whether you want to keep the same values for A and B, in which case you must then recalc the value for C.
    But then, you may also have a requirement that C must always be a whole number (since you can't sell half a handbag).
    In which case you roundup to the nearest whole number of Units Sold, and then recalc to arrive at the 'closest' value in E to your 'desired' value etc etc.

    I have attached an updated sample to deal with your 'no entry' request.
    It is easier to use this via a 'named' cell.
    Check out the formulas in D and F in attached sample file.
    When writing formulas in vba, it can get messy to include double-quotes " in the formula.
    So using 'named' cells can simplify this.

    When making design changes to the sheet, it is often easier to disable all event trapping while you make changes.
    So I put two buttons on to simplify this.

    zeddy
    Attached Files Attached Files

  10. The Following User Says Thank You to zeddy For This Useful Post:

    skipro (2015-02-15)

  11. #10
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Zeddy,
    Thanks for a solution to the div/0 error return, but my "retentive side" has me thinking about and researching this.

    Is it possible to incorporate the following code, or a variation, to deal with the div/0 issue?

    Code:
    Dim Cel As Range
     For Each Cel In Selection
     If IsError(Cel.Value) Then
     If Cel.Value = CVErr(xlErrDiv0) Then Cel.Value = "no entry"
     End If
     Next Cel
    Or, link to another script/macro which would on a div/0 error return a string?

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Skipro

    A lovely thank you. I appreciate that.

    Now, regarding your text for the div/0 error. You get a div/o error as result of having a formula in the cell.
    If you swap this formula with a text message "no entry", then what happens when the invalid source is no longer invalid????

    zeddy

  13. #12
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    zeddy,
    You are welcome.
    Do not all of your samples deal with that "swap" by replacing the formula or using the script so the override did not interfere?
    My thought was to add this script only to return the text if a div/0 error occurred, not if this error did not occur, which is what it does as far as I can see.

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Skipro

    I misunderstood. When I looked at your code snippet, I thought this was to replace ALL div/0 errors in a range of cells. My samples don't replace ALL formulas when a change is detected. For example, in sample-5.xlsm, if you change a value in C or D, the code doesn't re-write the formula in F.
    If you want to specifically differentiate the div/0 from the six other types of errors (e.g. #VALUE!, #NULL!, #REF!, #NAME!, #NUM!, #N/A!), then you would definitely need to use something like your code snippet, or perhaps something based on this:
    Code:
    For Each c In Cells.SpecialCells(xlCellTypeFormulas, 16)
    If c.Value = CVErr(xlErrDiv0) Then
    c.Value = "no entry"
    End If
    Next
    (this code only looks at cells which have errors, and then tests for the div/o error specifically)

    zeddy
    Last edited by zeddy; 2015-02-19 at 05:27.

  15. #14
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Zeddy,
    How do I add your last error script
    Code:
    For Each c In Cells.SpecialCells(xlCellTypeFormulas, 16)
    If c.Value = CVErr(xlErrDiv0) Then
    c.Value = "no entry"
    End If
    Next
    into your V.4?
    Please create v.6 adding this script to your v.4.
    Would this addition "break" anything in v.4?

    Thanks.

  16. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Skipro

    In my V.4, if you delete any single entry in the range [C4:C9], you will get a corresponding #DIV/0! error in [F4:F9]. The V.4 version was to allow a person to 'choose' a value for F, and have vba automatically adjust the required value for G to 'match' this chosen F value.

    In V.4, if you delete ALL the entries in the range [C4:C9], then you will also get all #DIV/0! errors in [D4]..[D9] as well as in [F4:F9], as well as in the sum cell [D11].
    It is easy to replace these #DIV/0! errors with a 'no data' message as described.
    But, the whole point is, what would you want to happen if you then say, manually enter a value of 20 in cell [F4]??
    If you have deleted all the values in [C4:C9] you have no data on which to base the required values.
    So you need to be very clear what your rules are.

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •