Excel Formula

chateauneufdupape

I am trying to make a spreadsheet that does a calculation from input data. The problem is that the data can come in three different formats depending on their source. There is a mathematical formula to convert between the 3 formats. I would like to have 4 cells, 1 for each of the 3 formats and the 4th cell for the output calculation. What I would like to be able to do is enter the data into any one of the three format cells, the other two cells would then calculate the value into their own format.

chateauneufdupape

A very interesting site which I have bookmarked, but I'm still no closer.

Tony_H_3

You'll need to use a nested "if" formula.

=if(A1=var1,"sum1",if(B1=var2,"sum2",if(C1=var3,"sum3","false")))

DVB_3

Can you provide an example?

Tony_H_3

Tony_H wrote:

You'll need to use a nested "if" formula.

=if(A1=var1,"sum1",if(B1=var2,"sum2",if(C1=var3,"sum3","false")))

Sorry, I miss read your question. The psuedo code above takes account of only 1 output in the 4th cell.

You have two options and they totally depend upon how you want it to look when no data is input, but the solution would still be similiar using nested "if" formulae in each of the three cells to establish whether a calculation needs to take place or not!!;)

chateauneufdupape

e.g. 5/1 , 6.000 , 500 are the three styles but are all the same for my further calculations.

So if I entered 5/1 in cell A1, cells A2 and A3 would display 6.000 and 500 respectively.

Or if I entered 6.000 in cell A2, cells A1 and A3 would display 5/1 and 500 respectively.

user1234_2

Afraid you are in danger of creating circular references with the setup you propose. I suggest you seperate your input from your formulae i.e. inputs go into columns 1-3 but calculations/convertion and final output goes in columns 4-6.

Good luck with the gambling

DVB_3

You have provided neither enough information nor formulas you want to program, so I'll try to devise a general solution. This is the way I see it - you'll need six columns to work with (remember - general solution, some specific cases might be easier to work with). So, we have A1, B1, C1, D1, E1, F1. Here A1, B1 and C1 are used for data entry, which is needed to avoid a number of potential errors (in general case). The following formulas should be used:

D1: =IF(NOT(ISBLANK(A1)),A1,(IF(NOT(ISBLANK(B1)),"Formula from B1",(IF(NOT(ISBLANK(C1)),"Formula from C1","Default value")))))

E1: =IF(NOT(ISBLANK(B1)),B1,(IF(NOT(ISBLANK(A1)),"Formula from A1",(IF(NOT(ISBLANK(C1)),"Formula from C1","Default value")))))

F1: =IF(NOT(ISBLANK(C1)),C1,(IF(NOT(ISBLANK(A1)),"Formula from A1",(IF(NOT(ISBLANK(B1)),"Formula from B1","Default value")))))

In every one of them you should replace "Formula from A1" with the actual formula to calculate your values for B1 and C1 from your A1 value (different formulas in general). The same should be done for B1 and C1 formulas, and the default value should be replaced by whatever you want to see in your cell if nothing has been entered.

Don't forget, as per your request formulas are mutually entry-exclusive, i.e. they rely on only a single value (either A1, B1 or C1) being entered.

user1234_2

That will work, nice one DVB.

But if I am cheeky I would say it could be more "elegant" if you switched the IF's around so that they used ISBLANK instead of NOT(ISBLANK)....the joys of Excel

DVB_3

user1234 wrote:

That will work, nice one DVB.

But if I am cheeky I would say it could be more "elegant" if you switched the IF's around so that they used ISBLANK instead of NOT(ISBLANK)....the joys of Excel

Agreed without reservations. As I said in my post, there are plenty of ways to improve once the problem has been properly formulated (which is not the case, unfortunately). I chose to proceed using NOTs because it's easier to understand and modify the formula(s) this way, as you work your way down straight, whereas pure ISBLANKs would negate all conditions, and as such conditions would have to be reversed and you'd end up working "bottom-up" , which is something harder to grasp.

chateauneufdupape

Thanks DVB and user1234. I think you have come up with a solution. I will try it tonight.