Hello,



I was wondering if anyone would be able to assist with an Excel formula.

Basically, cells A1-C1 contain drop-down menus.

A1 has 3 options: Morrisons, Aldi, Lidl.
B1 has 2 options: 2-4, and 5-7
C1 has 7 options: 1, 2, 3, 4, 5, 6, and 7.

If A1=Morrisons, B1=2-4, and C1=1 => The result has to be £5.
If A1=Lidl, B1=2-4, and C1=1 => The result has to be £7.
Etc.

I tried the following formula: =IF(AND(A1=Aldi, B1=2-4, C1=1), 7)
However, the result is always FALSE.

If possible, would anyone be able to help me work out the formula, please?

Thank you in advance!

Hi guys,

I managed to work out the formula for one of the shops.

=IF(AND(A1="Morrisons",B1="2-4",C1 < 5),"£5", "£6") [As I needed 5-7 to equal £6]

However, I'm still trying to figure out how to get different results for different shops.

Hi
Please post a sample sheet ( no pics please) with some data.Make sure your desired results are shown, mock them up manually if necessary.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Attached is an example with possible solution (as much of one as it was possible with so sparse info).

As there was no explanation how values in column C will affect the result (I got only, that when the value in column C is less or equal of 5, then it doesn't affect the result), I added a dummy part into formula, which will add 1 whenever the value in column C will be >5.

Essentially, the result is read from table in sheet ShopRanges.

Attached Files

CalulationExample.xlsx (16.7 KB, 6 views)

Hello,

I'm very new to Excel. It was just an example o see what formula should be used.

I have attached an example of what I need.
Workbook.xlsx

Basically, for the drop down menus, when I choose a client, a taxi size, and a taxi heads, I need the cost to return a specific value (eg £7).

I hope this makes sense.

Also, thank you for taking the time to reply to this

I updated my previous example accordingly your last workbook.

Again it is unclear for what the column TaxiHead is needed! I added it, and used it in calculations, but currently you can leave it out, and nothing changes!

Based on name, the column Reason probably contains some texts. As you want this text to be displayed too, I had to add a helper column RowNo into PriceList Table, and instead of SUMIFS() had to use INDEX() to retrieve result data from PriceList.

An advice - never place different Tables onto same sheet. When you do this, you practically restrict yourself from adding rows into Tables, or deleting them!

Attached Files

CalulationExample.xlsx (19.8 KB, 6 views)