Which formula do i need to calculate based on criteria

[sMart_D1006]

Hi all, I'm new to this forum and looking for some guidance. I know Excel but not up to speed with certain formulas.
Basically I have the following problem I'm trying to resolve.

On the sheet I am working on, which is an order form with one field having a figure excl. VAT and another Incl. VAT and depending on the item selected I need the correct figure populating in a certain cell. This is how the layout is and I need it to be.

C90 - I have text (example, apples is Excl. VAT and oranges is Incl. VAT)
V90 - Figure Excl. VAT
W90 - Figure Incl. VAT
H36 - Is where I need the figure to go.

So, basically if a excl VAT item is selected, how do i get the V90 figure to enter in H36?

Hope someone can help me out with this.

Thanks in advance

[etaf]

is there anything to say if VAT or NOT vat in the row
otherwise you are going to need a lookup table

a table of products and the codes for VAT / Non Vat
then lookup

[sMart_D1006]

I have the excl. VAT figure in V90 and in W90 i have the formula =V90*1.2.

Just really stuck on this one and can't figure it out.

[etaf]

a little confused
C90 - I have text (example, apples is Excl. VAT and oranges is Incl. VAT)

how does excel know to include or exclude VAT
if you have Apples - whats in V90 and in W90
if you have Oranges - whats in V90 and in W90


lets say Apple is 100 net same as Oranges
then apples would need VAT added so * 1.2
BUT oranges does not have VAT -
how does excel know that ?

[sMart_D1006]

It doesn't. Basically I have a table of all data then using VLOOKUP it pulls it though to another sheet that's in a format to print and puts the data in selected parts. That was the only way I could think of doing it but then got stuck buy getting the correct figure to populate where I need it. Basically if it says apples I need it to show excl. Vat and oranges incl. Vat. Does that make more sense???

[etaf]

not really
how does excel know Apples excludes VAT and Oranges include vat , pears, grapes, bananas etc etc
- how do you know what to include and what to exclude ?
excel has to know what to exclude and what to include
if that information is not in the table somewhere / some how or on another list excel would not know
nor would anyone else reading the spreadsheet , you must know your self - how do you know what to include and what to exclude ?

[Glenn Kennedy]

rather than guessing...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[sMart_D1006]

I have attached a sample of my spreadsheet and put a note on there, hopefully clear to understand to what I am trying to accomplish.

[AliGW]

Not quite sure what you are trying to do - what is your expected outcome and why?

[Glenn Kennedy]

How is Excel supposed to know whether or not VAT applies to apples? You need to have a table of commodities and yes/no for VAT liability. Then it's easy...

Something like this???

[sMart_D1006]

Thanks Glenn, that has worked perfectly. I didn't realise I would have to populate the figures next to each description as you have done in A19:B20. Least I know now for future reference.

Thank you all for your time and input.

[Glenn Kennedy]

You're welcome. If you do not have that information somewhere... Excel cannot work it out by itself. Thanks for the rep.