need schizophrenic formula [work both directions]

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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 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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

zeddy,
All the potential possibilities or rules are numerous, so let me limit them to the probable.
“C” will never be all “0”s. Usually none and a maximum of only a few out of about 12+.
V.4 & v.5 work well except when you overwrite F since it is not replaced. Not sure if this is fixable because you do not always want the overwrite to be replaced. For now all seems ok.
But the previous request is not about these possibilities but only to show me how you would add the last code to v.4 [or v.5].

Code:

 
For Each c In Cells.SpecialCells(xlCellTypeFormulas, 16)
If c.Value = CVErr(xlErrDiv0) Then
c.Value = “no entry”
End If
Next

I chose v.4 because it was simpler and easier for me to see script technique. It would not do anything the “Named cell” approach would do, just a different way to do it as I see it. I am curious to see how it would work.

Reply | Quote

Hi Skipro

have a look at this attached version.
To simplify things, I also put Data Validation to restrict entries in [C4:C9] and [G4:G9] (basically to disallow any ‘text’ entries)

zeddy

Reply | Quote

zeddy,
Double thanks. You are a true gentleman. I know you put a lot of time and effort into this. Greatly appreciated. This now has essentially all the functions I need. Unfortunately it is an ongoing project. For now we can consider it completed and closed. I have 1 more question but started a new thread.

Reply | Quote

zeddy,
Too good to be true. Found a problem.
You have F change with change in C including a text return for a div/0 error, as requested. Works fine as is. But in my real sheet, C is a SUMIF from N.
=SUMIF(M:M,”A”,N:N)
When I change N, C changes. It would appear to me that your script should then respond the same as if I changed C directly, since F depends on the value of C.
But this is what occurs. If I change N so the SUM is 0, C changes to 0 [as it should] and F returns the error text as planned. But when I change N so its SUM is no longer 0 and therefore C no longer is 0, F does not update, the text remains. So C goes from 0 with error message in F as it should, to C having a value not 0 but F does not recalculate and leaves the error text.
I have to change C directly to refresh F as planned.
Can you help me with this?

Reply | Quote

Hi Skipro

OK, if you now want the SUMIF formula for your C, then we can do what you want i.e. when entries in M and/or N are changed, then this is reflected properly in C, D, F and G
However, this really means that C should not now be ‘manually overwritten’?????

zeddy

Reply | Quote

zeddy,
Yes, in the “real world”, I do have C & D locked.
Can this be done using your script so C & D can change through the changing of M & N [SUMIF], but not by over-writing the cells in C & D? I think this will create less future hassles doing it this way versus “protecting” the worksheet.

I was surprised that deriving values for C through a formula [SUMIF] versus by direct inputting a value into cell would have any effect on a script that reads the value of C for another function. Still do not understand why that is.

Reply | Quote

Hi Skipro

..I’ll have the updated file posted here tomorrow.

zeddy

Reply | Quote

Hi Skipro

In this version:
1. You can change the entries in range [A4:A9]
>example, change [A5] from B to W; now change [A5] from W to D, then change from D to B
>example, put K into cell [A8] etc etc.

2. If you overwrite any cell in range [C4] to [D9], your action is ignored i.e. your entry is replaced back with the formula

3. If you change entries in M or N, the results are shown in C, D ,F and G

4. If you ‘arrange’ it so that the computed value in C is zero, the corresponding entry in F is ‘no entry’
> you cannot delete the cell with value ‘no entry’; if you add an appropriate (non-zero) value in N, the relevant ‘no entry’ will be updated.

Please let me know if this is getting nearer.

zeddy

Reply | Quote

zeddy,
I have spent a lot of time trying to modify your suggestions to work with my sheet. Finally got all to function as you and I intended.
At this point only 1 problem has arisen.

Formula for C uses A – C=SUMIF(M:M,Ax,N:N) which I also think is the best way to go.
My original formula used the specific string which was derived from A – SUMIF(M:M,”string from Ax”,N:N)
If the string is the same as Ax, then your formula/script for C would be fine.
But my Ax is essentially the same but includes a description of Ax preceeded/separated by a space.
Ax has 1 or 2 letters, space, description::
A [space] description for A
BB [space] description for BB

How would we modify
zFormula = “=SUMIF(M:M,A” & zRow & “,N:N)”
to use the letter before the space only, removing the space and description?
zFormula = “=SUMIF(M:M,A[before space]” & zRow & “,N:N)”
Basically the same idea as this, “I THINK…”
=LEFT(Ax,FIND(” “,Ax)-1)

Also:
Why did you use for:
Case [c1].Column:
zFormula = “=SUMIF(M:M,A” & zRow & “,N:N)”
Cells(zRow, “C”) = zFormula[/B][/COLOR]
instead of:
Cells(zRow, “C”) = “=SUMIF(M:M,A” & zRow & “,N:N)”
which was the format you used for the rest of the script. Why the difference?

Reply | Quote

Hi Skipro

Instead of
=LEFT(Ax,FIND(” “,Ax)-1)
I would prefer..
=TRIM(LEFT(Ax,2)
..because this doesn’t require us to use double quotes when building the formula in VBA.
(You can build formulas ‘which require double quotes’ in VBA, it’s just messy)
Essentially, the =TRIM formula always takes the first 2 characters, then, if the second character happens to be a space, the TRIM will get rid of it.
The version 8 attached uses this method.

As far as using
zFormula = xxxxx
..and then
Cells(zRow, “C”) = zFormula
..this is just ‘cosmetics’.
I like to keep code lines as short as possible.
And also, using this method, if you were ‘stepping’ through the code, you could check the contents of variable zFormula. This allows you to double-check you’ve got it defined correctly.
Whereas if you use
Cells(zRow, “C”) = “=SUMIF(M:M,A” & zRow & “,N:N)”
..stepping through the code doesn’t help you much on that line.

In reality, I rarely ‘build’ formulas directly in VBA.
I usually have any ‘required’ formulas typically defined in ‘hidden’ row 1, then use VBA to ‘copy’ the required formula(s) to required locations. this makes it easier to modify the formulas without having to re-write the VBA code. And more importantly, if we were to used named cells (or named ranges) for such formulas, then even if we add new columns etc, the VBA still doesn’t require maintenance.

So, another way of doing what you want is perhaps to use a formula in col to get the ‘lookup’ characters you want from A, and then use B in the code as in..
zFormula = “=SUMIF(M:M,B” & zRow & “,N:N)” ‘=SUMIF(M:M,B4,N:N)

zeddy

Reply | Quote

zeddy,
In:
zFormula = “=SUMIF(M:M,TRIM(LEFT(A” And zRow & “,2)),N:N)”
Did you mean the “And” or did you want “&”?

I originally saw the “And” and wondered why but assumed you had a good reason. Had all sorts of weird issues until I exchanged “And” with “&”. Real head scratcher. If “And” was intended, then we will have to figure out what is awry.

In my sheet I limited changes below row22 to avoid issues.
If zRow > 22 Then Exit Sub
But for Columns M & N ONLY I would like to extend it to 35. Can this be done?

You explained why you used the variable “zformula =” for cosmetics and neatness, makes sense. Why then did you not use it throughout? You only used it for C.

Reply | Quote

Hi Skipro

oops. how did I miss that??? Must’ve been some kind of lapse.
Of course, it should have been &.
Trying to post back too quickly!

Yes, you can test for row > 22 etc for specific columns.
See attached.

With regards to using zFormula=
..the idea is to show you different techniques.
..once, you decide on your preferences, then yes, it’s a good idea to be consistent.

zeddy

Reply | Quote

zeddy,
All looks good now, but 1 thing comes to mind. It would be very helpful if I can override the DIV/0 error in F when C=0.
F=(C/D)*G
I still want the error to return a string as is coded now, but I would then like to be able to over write this error return/string with a number since that is the intent of this column, as it is used for other “what if” calculations. I can fudge it by putting a 1 [or any number] in M so C is not 0 and therefore no error, but an over write ability in F would be more accurate and more eloquent.

Reply | Quote

Hi Skipro

So, when you overwrite the F value of ‘no entry’, say with $10, you then have this situation:
C = 0
D= 0.00%
F= $20
G = 25.00% (or some other previous value)
We have F=(C/D)*G
so.. 20=(?/?)*0.25

Since we don’t know what value to ‘make’ C, the current version 9 doesn’t allow you to change the F value of ‘no entry’. If you try and ‘overwrite’ this F value, it just puts the ‘no entry’ back.
Now, you could say..
‘well, if I change the F value of ‘no entry’ to any value, I will add a default value to the bottom of cols M and N
to give a value of xxx in C”

How do you see it????

zeddy

Reply | Quote