Emma has attended:
Excel Intermediate course
Effective Communication Skills course
Excel Advanced - Formulas & Functions course
If & And
Hello
I have 3 columns of data, showing different costs from 3 different vendors, I need to work out which is the cheapest vendor and by how much, shown as a percentage.
Vendor 1, Vendor 2, vendor 3,
Trying to use an "if" function but I think I need an,βifβ and βAndβ nothing seems to work.. any help would be appreciated
Thanks Emma
RE: if & And
Hi Emma
Thanks for getting in touch. You could achieve this with a nested IF but there's a little known function in Excel called SMALL which will be a big help. It finds the "x" smallest value in a list. So to find the second lowest in A2, B2 and C2 you can write:
=SMALL(A2:C2,2)
First a quick reminder on the percentages. Assuming your lowest Vendor price is in cell A2 and the next lowest is in B2, you'd write:
=(A2-B2)/B2
Now to put SMALL together with your percentage division and you get:
=(SMALL(A2:C2,1)-SMALL(A2:C2,2))/SMALL(A2:C2,2)
I think that should work. Give it a try and let us know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: if & And
Thanks for your speedy reply this will come in very useful, I will give that a go..
But I should have explained, I have to list which vendor is the cheapest, thats why I thought an if function ..
Would you show me the nested If function?
Best Emma
RE: if & And
Hi
Thanks for your reply. If you then want to work backwards and find out who was the cheapest, you'll have to use MIN, INDEX and MATCH.
=INDEX(A1:C1,0,MATCH(MIN(A2:C2),A2:C2,0))
Find the lowest, find that number's position in the list, then find it's relative header. You will have to adjust that first zero for how many rows away the header / label is. If it's 4 rows above, enter -4.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
