Formula With Different Conditions

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,080
Hi i want tto create a formula using the following conditions, I've tried myself but to no avail. I hope somebody on here can help me.



K10 can have the values "W", "EW 1/4" & "EW 1/5" and M10 the values "W", "W-DH", "P","P-DH" & "L".

IK10 is "W" and M10 is "W" i want the sum (H10*J10) but if M10 is "W-DH" i want the sum ((H10*.5)*J10) with "L" leaving 0.

If K10 is "EW 1/4" or "EW 1/5" and M10 is "W" i want to sum (O10+P10) but if "P" is in M10 then i just want the value of P10 and again "L" leaving 0.

If K10 is "EW 1/4" or "EW 1/5" but M10 is "W-DH" then i want the sum (Q10+R10) but if "DH-P" is in M10 then I want to return the value of R10 with "L" leaving 0.


I hope this is clear.

Thanks
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,080
its a betting spreadsheet, so "L" means the selection lost leaving 0 (no returns)
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
But what does it mean in terms of the the formula?

IK10 is "W" and M10 is "W" i want the sum (H10*J10) but if M10 is "W-DH" i want the sum ((H10*.5)*J10) with "L" leaving 0
If K10="W" and M10 ="W-DH", you want to return (H10*.5)*J10, right? Where does "L leaving 0" come into it?
 

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,080

ADVERTISEMENT

If K10="W" and M10 ="W-DH", you want to return (H10*.5)*J10, right?

yeah thats right

but if any other value other than "P" or "W" is entered into M10 then I want the value always to return 0. "L" will alway be the other value that can be entered in M10.

I have this original formula which works great

=IF(K10="W",IF(M10="W",H10*J10,0),IF(M10="W",O10+P10,IF(M10="P",P10,0)))

but now I want to add the condition: If K10 is "W" and M10 is "W-DH" then the sum is ((H10*.5)*J10)
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Strange how it took 2 questions for you to reveal you already had a formula that worked up to a point!

Based on your explanation, the following should work, but I'm unable to test it since you haven't posted a sample of your data...

Code:
=IF(K10="W",IF(M10="W",H10*J10,IF(M10="W-DH",H10*0.5*J10,0)),IF(M10="W",O10+P10,IF(M10="P",P10,0)))
 

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,080
WORKS GREAT MATE THANKS!!

Strange how it took 2 questions for you to reveal you already had a formula that worked up to a point!

I posted the formula in another thread last night but was getting nowhere, so I thought I'd start again from the beginning thats all.

Once again thanks!!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,164
Messages
5,768,561
Members
425,481
Latest member
ihumanl

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
Top