receipt error

[helpmex]

cud anyone fix my receipt up, i want the values to come up without saying na and add up the total with only the values selected

problem = error comes up when values are entered, total only works when all the fields are filled in, i want it to work without all the fields having to be filled in

http://aycu27.webshots.com/image/337...7474728_rs.jpg


download the excel file to try fix

[EdMac]

Hi Helpmex

Do it like this

=IF(A21="","",VLOOKUP(B21,Price, 2, "false"))

Ed

[pinmaster]

Hi,

Here's one way:
in E39

Please Login or Register  to view this content.

but a better solution would be to get rid of the #N/A errors completly, replace the formulas in C20 and E20 with the ones below.

in C20:

Please Login or Register  to view this content.

in E20:

Please Login or Register  to view this content.

HTH
Jean-Guy

[helpmex]

wer do i paste it, coz it went wrong when i did it before

edit : doesnt affect it, still says na on subtotal

[helpmex]

but the subtotal still says na

and that forumula hasnt done nothing

i have uploaded a new excel doc, please use that one

[pinmaster]

Hi,

Just a note, in your VLOOKKUP formula the FALSE statement should not be inside quotes and can also be replaced by a 0 for FALSE or 1 for TRUE.

Cheers!
Jean-Guy

[helpmex]

im crap at excel, all i want is my sub total to appear when there is a total, it only appears when all fields are completed

anyone tell me the forumulas for my new doc i posted

[pinmaster]

Hi,

See the first formula I gave in my first post, just copy it and paste it in cell E39

Jean-Guy

[helpmex]

i have changed the excel doc, look at it and give the new forumula

[pinmaster]

Hi,

in D39 put:

=SUMIF(D20:D37,"<>#N/A",D20:D37)

in D40 put:

=D39*0.1

in D41 put:

=D39-D40

in you want to get rid of the #N/A errors then in C20 put:

=IF(A20="","",VLOOKUP(B20,Price, 2, 0))
and copy down to C37

in D20 put:

=IF(A20="","",(A20*C20))
and copy down to D37

all the formula says is that if A20 is empty then do nothing, else calculate!

HTH
Jean-Guy

[helpmex]

THANK YOU

WORKS PERFECT Xd

[pinmaster]

You're Welcome!

Regards!
Jean-Guy

[stevekirk]

HI,

IN C20 PUT

=IF(B20="","",VLOOKUP(B20,Price,2))

IN D20 PUT

=IF(B20<="","",(A20*C20))

THEN COPY THESE FORMULARS DOWN ALL THE ROWS IN COLUMN C AND D

STEVE

[EdMac]

Sorry,

I really can't believe that I put

=IF(A21="","",VLOOKUP(B21,Price, 2, "false"))

True and false NEVER have speech marks around them.

Ed