Thanks:  0
Likes:  0

1. Hi all...

I am looking for a formula which calculates dependent upon the rest of the columns. Here's what the headers in the spreadsheet looks like:

1st|Mth|2nd|Int|Hit|A/B|Bar|Low|High|Kill|Amt|Saved

If under "Bar" TO is typed then "Amt" is equal to "Saved"

If under "Bar" ANN is typed then set Saved to *

If under "A/B", A is typed and in Bar "KRO" is typed then Saved equals to the absolute ("High" - "Hit")*(Amt)/(High)

If under "A/B", B is typed and in Bar "KRO" is typed then Saved equals to the absolute ("Low" - "Hit")*(Amt)/(Low)

I would really appreciate any help. And I hope the above makes sense!

2. I think this can easily be done, but I think the columns/directions need to be clearer, can you try again please?

The headers of the spreadsheet are as follows (with "|" as a separator):

1st | Mth | 2nd | Int | Hit | A/B | Bar | Low |High | Kill | Amt | Saved

If under the header, Bar, the value "TO" is typed then the header, Amt = Saved

If under the header, Bar, the value "ANN" is typed then the header, Saved = Null

If under the header, A/B, the value "A" is typed and in Bar, "KRO" is typed then the header, Saved = ABS("High"-"Hit")*("Amt")/("High")

If under the header, A/B, the value "B" is typed and in Bar, "KRO" is typed then the header, Saved = ABS("Low"-"Hit")*("Amt")/("Low")

FYI - Headers: 1st, Mth, 2nd - are dates.

Int, A/B, Bar, Kill - are strings

The rest are all numerics.

So, for example, if A/B is "A", Bar is "KRO", High is "140", Hit is "130" and Amt is "50" then:

140 - 130 = 10,
now 10 * 50 = 500
then, 500 / 140 = 3.6
Saved = 3.6 (to 1 d.p)

Hope this is ok.

4. Hope this is ok.

Not quite, Ben, at least for me.

May I suggest that you select an empty cell, type = in that cell, select 10 rows of your data including the labels/column headings, hit F9, copy what you see, and paste it in the follow up, and tell explicitly where what must be computed?

5. Ok, I'm assuming that your columns for 1st thru Saved are A thru L

The formula for Saved (Column L) would be....
=IF(G2="ANN",0,IF(AND(G2="KRO",F2="A"),ABS(I2-E2)*(K2/I2),IF(AND(G2="KRO",F2="B"),ABS(H2-E2)*(K2/H2),0)))

The issue of "If under the header, Bar, the avlue "TO" is typed then the header Amt = Saved" could possibly create a circular reference with column L if you are not overwriting the formula to check if "BAR" = "TO" with a number......would have to see your data to understand that one. the formula for column K would be .... =IF(G2="TO",L2,0)

Hope I understood it right......if not, yell in the next 2 hrs while I'm at work and I'll try again.

6. Wow! Thanks Wi, thats great!

Couple of more queries, can I get round the circular reference issue, i.e. have '=IF(G2="TO",L2,0)' in the same formula.

Also, how can I make the formula apply to all of column L?

Once again, thank you.

7. Sorry, Wi_Guy, one more thing:

I add a new line between rows 4 and 5, so I get a new, empty, line 5, but the formula does not seem to recognise that line. Could you help on this, please?

8. Wi_guy, looks like I've worked out the circular formula issue - thanks.

But, still require help with applying the formula to all rows for column L.

## User Tag List

#### Posting Permissions

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