If formula problem

  • Thread starter Thread starter Phoenix
  • Start date Start date
P

Phoenix

I need the formula to do the following:
if i mark an "x" in E18 if i sell motorbikes today, and the total wil
automatically be C18*D18*E9=total. If i buy motorbikes today i need th
same operation, but the total will be in minus(C18*D18*E10=-total).
need a formula that covers all the "x" (for today and tomorrow), bu
always just one "X" in the same line

I used something like this formula before, with "index" and "match
operations.

Any suggestions guys?


Phoeni

Attachment filename: if formula.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46508
 
Hi Phoenix,

How about

=C18*D18*(OR(E18="x",F18="x"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Not quite....cause the "x" refers to the factors over. when we make a
"x" for example in E18, the result will be C18*D18*E9. That is if w
sell. When buying the result will the factor for selling.

Phoeni
 
That's not what is says on the website.

The thing I don't get is how to know if it is buy or sell, if it is cars or
bikes, what data item says that. The basic formula just needs the factor
adding

=C18*D18*(OR(E18="x",F18="x"))*E9 (or is it E8, F8, F9, E10, F10, E11, F10,
E11, F11, E12 or F12?).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Think you'd need additional inputs / specs
for the Vehicle Type [bike or car]
and whether it is a Buy or Sell

Try this additional set-up in Sheet1

Put in H9:H12
: MBS, MBB, MCS, MCB

Put the labels
in A17: Vehicle Type
in B17: Buy / Sell

In A18:A20 will be the inputs / specs: MB or MC
[where MB = Motor Bike, MC = Motor Car]

In B18:B20 will be the inputs / specs: B or S
[where B = Buy, S = Sell]

Put the labels "Today" and "Tomorrow" in E17:F17

------------
Put in G18
=IF(AND(E18="x",F18="x"),"Error",IF(E18="x",C18*D18*OFFSET($H$9,MATCH(A18&B1
8,$H$9:$H$12,0)-1,-3),IF(F18="x",-C18*D18*OFFSET($H$9,MATCH(A18&B18,$H$9:$H$
12,0)-1,-2),"")))

Copy G18 down to G20

Provided the additional inputs / specs are made in A18:B20,
G18:G20 will return the correct calculated result
based on the single* "x" in either E18:E20 or in F18:F20
[ i.e. as specified in your post ]


Error alerts provided in the IF formula in G18:G20
 
Sorry, Bob P, if I said anything wrong. The things is that i need to
build up a system, and I'm not sure what i'm looking for. Bikes/cars
and buy/sell is just a suggestion from my side on how to start to build
this system :-)


Max: Thx for your answer :-) That's what I call a really good answer!
And it seems i'm closer to a solution now


Phoenix
 
Back
Top