Conditional Formula

BenNV · Mar 28th, 2002, 04:02 AM

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!

Many Thanks in advance, Ben

wi_guy · Mar 28th, 2002, 07:43 AM

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

BenNV · Mar 28th, 2002, 08:09 AM

Thanks for your reply.

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.

Aladin Akyurek · Mar 29th, 2002, 03:17 AM

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?

aladin

wi_guy · Mar 29th, 2002, 07:17 AM

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.

BenNV · Apr 2nd, 2002, 01:20 AM

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.

BenNV · Apr 2nd, 2002, 01:38 AM

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?

BenNV · Apr 2nd, 2002, 02:10 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 04:02 AM	#1
BenNV Board Regular Join Date: Mar 2002 Posts: 160	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! Many Thanks in advance, Ben

Mar 28th, 2002, 07:43 AM	#2
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	I think this can easily be done, but I think the columns/directions need to be clearer, can you try again please?

Mar 28th, 2002, 08:09 AM	#3
BenNV Board Regular Join Date: Mar 2002 Posts: 160	Thanks for your reply. 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.

Mar 29th, 2002, 03:17 AM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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? aladin

Mar 29th, 2002, 07:17 AM	#5
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	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.

Apr 2nd, 2002, 01:20 AM	#6
BenNV Board Regular Join Date: Mar 2002 Posts: 160	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.

Apr 2nd, 2002, 01:38 AM	#7
BenNV Board Regular Join Date: Mar 2002 Posts: 160	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?

Apr 2nd, 2002, 02:10 AM	#8
BenNV Board Regular Join Date: Mar 2002 Posts: 160	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.