How do i write an excel formula that can work either ways?

[chimezie oleka]

I want to write an excel formula that can compute commission charged using simple interest rate. The fields will be:

1. Amount
2. Percentage
3. Commission charged

How do i write a formula that can provide answers to any field once the other two fields have been completed?

 

[Kamolga]

if amount is A1, % A2 and Commission A3

=IF(A1="",A3/A2,IF(A2="",A3/A1,A1*A2))

You might have to change , into ; depending on your language in Excel

 

[chimezie oleka]

Hello kamolga, will I have to replicate this fomular in the A1,A2 & A3 cells? Which cell will this code be entered?

 

[chimezie oleka]

if amount is A1, % A2 and Commission A3

=IF(A1="",A3/A2,IF(A2="",A3/A1,A1*A2))

You might have to change , into ; depending on your language in Excel

Please in which cell will this code be entered?

 

[Kamolga]

Hello kamolga, will I have to replicate this fomular in the A1,A2 & A3 cells? Which cell will this code be entered?

No, in cell A1 you type any amount, A2 any discount, A3 Any commission but leave one of them blank. You can put that formula in any cell (C5 for example), as long as it is not A1, A2 or A3 and it will show the missing part: if you put a amount in A1 and a percentage in A2, it will show the value of commission. If you put values in A1 and A3, it will show % (decimal). Basically if you put 2 values in any A1, A2, A3, it will show the missing one

 

[Kamolga]

If it does not work, it means it has to be ";" instead of ","

[LEFT][COLOR=#333333][FONT=monospace][I]=IF(A1="";A3/A2;IF(A2="";A3/A1;A1*A2))[/I][/FONT][/COLOR][/LEFT]

 

[chimezie oleka]

It did not work. However, I somehow got round it through several experiments. Thanks.

 

[jolivanes]

@chimezie oleka
If you have it solved, could you be so kind and publish your outcome here for anyone searching to solve the same or similar problem.
Thank you very much.

 

[Kamolga]

It did not work. However, I somehow got round it through several experiments. Thanks.

I am glad you have a solution but the formula works perfectly.

 

[chimezie oleka]

Suppose; A1=Amount, A2=℅ and A3=commission charged.

Your objective to a formulae that can return the answer to any one of the variables provided the other 2 variables are known.

Code 1 : = IF(A1="",A3/A2,A1)
Code 2: = IF(A2="",A3/A1,A2)
Code 3: = IF(A3="",A1*A2,A3)

Enter each of these codes in any 3 cells where you want the results to be displayed.

For instance:
With this code, if you enter amount in A1 and commission charged in A3, the percentage applied will be displayed as a result in the cell where code 2 was inserted.