Conditional Formula

BenNV

Board Regular
Joined
Mar 27, 2002
Messages
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
I think this can easily be done, but I think the columns/directions need to be clearer, can you try again please?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top