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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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?
 
Upvote 0

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,080
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)
 
Upvote 0

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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)))
 
Upvote 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!!!
 
Upvote 0

Forum statistics

Threads
1,187,190
Messages
5,962,120
Members
438,586
Latest member
flickalok

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