Urgent - simple excel formula needed please

u587162
u587162 used Ask the Experts™
on
Hi

Got a urgent question.

Could someone please help me write a basic formula in column D of the attached.
Basically I need the value to appear in each row of col D, so that the value is equal to either col B's percentage of the corresponding value in Col A or the value in col C.

Example for row 3.

7.9m x 8% = 0.632m.  This is lower than the 5m in Col C, so the value in Col D should be 0.62m

If Col C's figure was lower, then this figure would be taken.  I cannot remember how to use the MIN command here.  I think it is =MIN((B3*A3),C3)?


Thanks.
test-data.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Network Administrator
Commented:
=IF(A3*B3<C3,A3*B3,C3)   Past this is D3 and copy down

Author

Commented:
This will have exactly the same effect as my formula above though right?
Gary DewrellSenior Network Administrator

Commented:
It will compare the value of A4 x B3 against the value in Cell C3. Which ever value is smaller will be placed in D3.

I believe that is what your looking for. If not let me know.

Thanks!
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Author

Commented:
Yes it is, but what I am asking is, your formula will have the same effect as =MIN((B3*A3),C3) as I posted in the question right, just another way of writing the formula?

Commented:
then would be =MIN((A3*B3);C3)
(not ,C3 but ;C3)


Same result

Author

Commented:
What difference does , or ; have?  Still works with ,?
Gary DewrellSenior Network Administrator

Commented:
Yes you can also use. =MIN(A3*B3,C3)

Commented:
, doesn't work with me (Excel2010)

Author

Commented:
semi colon doesnt work, only comma works with excel 2007...or for me it does anyway.
Gary DewrellSenior Network Administrator

Commented:
I am using 2010 also and it is working. Can you post your file again.
Did you check the file I attached?
Gary DewrellSenior Network Administrator

Commented:
Sorry, missunderstood what you said. I thought you added the code I provided and it did not work.

Your sheet is working fine for me.   What results are you seing in Collum D?

Author

Commented:
=IF(A3*B3<C3,A3*B3,C3)  

Just to close off this one, how would I amend this statement so that it doesnt show #N/A or leave the cell blank if the other cells in the formula are blank?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial