Special formula help please

[fanderduck]

This is an example of what I'm seeing. Under USD TOTAL ($13.00 PESOS) it is not showing a numerical value, it shows True. I need it to show the calculated numbers. Is there a VB code I can use to get this outcome? The formula I am using is "=IF(F6="usd",SUM(N6,O6,P6,,Q6,M6),IF(F6="peso",SUM(M6,N6,O6,,P6,Q6)/13,""))".

<TABLE style="WIDTH: 737pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=982><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 36pt" height=48><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0 0.5pt; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 36pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl63 height=48 width=64>Date</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64 width=64>Company ID</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64 width=64>Trans Type</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64 width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64 width=64>Currency Type</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64 width=64>Receipt #</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 68pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl63 width=90>Description</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl63 width=72>Cash</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64 width=64>Vehicle Expense</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64 width=64>Air Expense</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64 width=64>Hotel Expense</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64 width=64>Meals</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 73pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl63 width=97>Entertainment</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl65 width=83 align=middle>USD TOTAL ($13.00 PESOS)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0 0.5pt; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl66 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl66> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68> PESO </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl70>1</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl71> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl72> $ 100.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl73> $ 1.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl73> $ 1.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl73> $ 1.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl73> $ 1.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl73> $ 1.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl90 align=middle> TRUE </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl82></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83></TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0 0.5pt; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl74>USD TOTAL</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #e6b8b7; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl75> $ - </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl88>Sub Total </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0 0.5pt; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl76> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85></TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0 0.5pt; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl74>PESO TOTAL</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #e6b8b7; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl75> $ 7.69 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl88>Advances </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl77> $ 0.59 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl86></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl86></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl86></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl86></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl89>TOTAL </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79> $ (0.59)</TD></TR></TBODY></TABLE>

 

[hiker95]

fanderduck,

By your screenshot we can not tell what the rows and columns are.


Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[dabbler68]

Hey Fanderduck,
Just copy and paste the following and you should be fine

=IF(F6="USD",SUM(N6,O6,P6,Q6,M6),SUM(M6,N6,O6,P6,Q6)/13)

All it does really is says go ahead and take a sum of the numbers, and if F6 doesn't say USD, divide by 13.....which is what I assume you want?

HTH's

 

[mikerickson]

Perhaps
=SUM(M6:Q6) / LOOKUP(F6,{"!", "Peso", "USD"},{1, 13, 1})

To expand to other currencies, the first array has to be sorted ascending.

 

[fanderduck]

I appreciate the reccomendation but dabbler68 answered it for me. I will use the BOX from now on.

 

[fanderduck]

Hey Fanderduck,
Just copy and paste the following and you should be fine

=IF(F6="USD",SUM(N6,O6,P6,Q6,M6),SUM(M6,N6,O6,P6,Q6)/13)

All it does really is says go ahead and take a sum of the numbers, and if F6 doesn't say USD, divide by 13.....which is what I assume you want?

HTH's

Thank you dabbler68

 

[dabbler68]

YQW fander, happy to help!

 

[fanderduck]

fanderduck,

By your screenshot we can not tell what the rows and columns are.


Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

Here is the link to the file.
http://www.box.net/shared/f8gb5h923z

But now that my current problem has been solved, I will need the option to add different curency types in the very near future. Is there a way to accomplish this with one formula in one cell?