Three cell values (Percentage) always sum up to 100% by entering the 2nd value

Dear Excel friends,

I have probably a simple Question.
In Cell A1,A2,A3 i can enter percentages. The summation of those cells always equals to 100%.

E.g. By entering A2 (20%) and A3 (30%) vba should fill cell A1 automatically with 50%.
E.g. By entering A1 (35%) and A3 (35%) vba should fill cell A2 automatically with 30%.
....................

Can somebody help me?

with kind regards,

Marc

How about a1=1-a2-a3?

Format A1 as percent

Km,

correct. But a matrix deletes the cell values (A1:A3) regulary. So this formula should be entered in vba. But how?

Sub Macro1()
ActiveCell.FormulaR1C1 = "=1-R[1]C-R[2]C"
Range("A1").Select
End Sub

Km, thanks a lot for your reply.
I don t understand it completly. So i put my code down here.

The input cells are actually cell range (D30:D32). So i changed the code to the following.

Sub
ActiveCell.FormulaR30C4 = "=1-R[31]C[4]-R[32]C[4]"
Range("D30:D32").Select
ActiveCell.FormulaR31C4 = "=1-R[30]C[4]-R[32]C[4]"
Range("D30:D32").Select
ActiveCell.FormulaR32C4 = "=1-R[30]C[4]-R[31]C[4]"
Range("D30:D32").Select
End Sub

Or is this i wrong interpretation.
gr marc

This Macro works on any cell not A1

Sub Macro2()
'
' In any row or column starting with 1 subtract whats in the row below same column and subtract whats in the second row below same column
'
' ActiveCell.FormulaR1C1 = "=1-R[1]C-R[2]C"
ActiveCell.Select
End Sub

If this doesn't solve it. Attach a copy of your spreadsheet

This is a nice macro, but not really what i m looking for. Sometimes you fill in cell A1 and A2, then it should give the value of cell A3. Sometimes you fill in cell A1 and A3, then it should give the value of cell A2. And sometimes you fill in cell A2 and A3, then it should give the value of cell A1.

gr marc

I didn't read your original post correctly. Try this
Sub Macro99()
'
' Macro99 Macro
' '

'
ActiveCell.FormulaR1C1 = "=1-R[1]C-R[2]C"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=1-R[-1]C-R[1]C"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=1-R[-1]C-R[-2]C"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Initially there will be a circular reference because each cell references each other. However, as you substitute the values for the other two cells the circular reference goes away as the formulas are overwritten by numerical values

I think you are looking for a smart macro that determines which of the last two cells in that group were changed, then modifies the third cell that has not been updated. This is a little tricky since it will need to make some assumptions. For example, what do you want it to do when:

- the user changes the first number in this group? I assume it needs to wait for one of the other numbers to be input before filling in the third cell. Thus, there could still be an error if the person makes a change in one cell and then saves the worksheet.
- what if they change a cell in this group, and then go work on some other cells not in this array? It will have to wait, or can it assume one of the other cells in the array is okay?

Pauley

Indeed it will be a smart macro.
- However, after the sheet is filled in the data will be transfered to another spreadsheet. The cell values will be deleted as well.
- All the cells need to have a value, otherwise another macro will not transfer the data. (So 2 cells should be filled in.)

I hope this is more clear for you. I add another spreadsheet to my response.

gr marc

Somebody any idea?

gr marc

Thanks a lot,

this works better. But is it possible to delete all the three values when one of the values (which has already a input) is changed?

gr marc

JosephP awseome. Thanks a lot.

It works perfect.

gr marc

Great Code. I have been looking for something like this for a while now.

I tried using this code on a blank macro-enabled worksheet, and I can't seem to get it working or picking up any change events. Using Excel 2013 if that helps.