Excel Mein Colculation

[Arshad2579]

Dosto Umeed Hai Sub Theek Hoon Ge.
Main Ne Excel Ki 1 Sheet Mein Koch Data Likha Hai.
Mujhey Is Mein Avg Aur Total Nikalna Hai.
Aveg Mein /km Bi Colculate Ke Sath Aa Jay.
Aur Total Mein Sirf No.s Ki Hi Colact Hoon
Koi Dost Help Kray ke Ye Sheet Kesy Hall Ho Gi.

[syar]

I assumed your A1:A4 consists your data like

12Km
13km
14km
15km

2nd text always occurs at the end of the value.

in B1 copy paste for sum

=SUMPRODUCT(LEFT(A1:A4,MMULT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,{0,1,2,3,4,5,6,7,8,9},"")),{1 ;1;1;1;1;1;1;1;1;1}))+0)

This is an array formula must be confirmed with CTRL+SHIFT+ENTER not just enter.
for average just devide above formula with COUNTA function or devide the value by simple count formula.


jsut change ranges/references accordingly. If any problem please explain
Best regards,

[Arshad2579]

Plz See Again..
And Help Me.

[Arshad2579]

Plz See Again

[syar]

Plz See Again

Sorry. M n pehle aap ki attached ki hoe file ki taraf khyal he naya gaya. you just need to change ranges.

in B7 and copy accross

=SUMPRODUCT(LEFT(B2:B6,MMULT(LEN(B2:B6)-LEN(SUBSTITUTE(B2:B6,{0,1,2,3,4,5,6,7,8,9},"")),{1;1;1;1;1;1;1;1;1;1}))+0)&"/Km"

again confirm with CTRL+SHIFT+ENTER

I have a suggestion for you if dont mind. Agar aap ko har numeric k sath km lekhna ha tu q na aap cell ko custom format kar lain. Jin cells ki custom formatting karainge un sab m "/Km" lekha hua aae ga aur print bhe ho ga. formula bhe kam kare ga aap simple sum average ya koi bhe calculation kar lainge. is tarah k formula ki zarorat nahee pare ge. Aap apne requrement dekh lain. phr jese aap chahain. custom formatting m masla ho tu bataye ga.


regards

[Arshad2579]

1st tu Aap Mujhey custom Cell mujhey krna bta dain q k main Kafi baar try kr choka hoon lekin end mein ye "/km " nahi banta

2nd Total (B7+C7+D7+E7+F7)cells ko bi calculate krna hai..

[Arshad2579]

........

[syar]

1st tu Aap Mujhey custom Cell mujhey krna bta dain q k main Kafi baar try kr choka hoon lekin end mein ye "/km " nahi banta

2nd Total (B7+C7+D7+E7+F7)cells ko bi calculate krna hai..

see the attached file. custom formatting ko bhe dekhya ga
regards,

[Arshad2579]

BoHaT ShoKrIa Problam Salved

[Asham Khan]

see the attached file. custom formatting ko bhe dekhya ga
regards,

Can You Explain Plzzzzzzz

I Am Applying The Same Formula But Showing Answer 0.

[syar]

Can You Explain Plzzzzzzz

I Am Applying The Same Formula But Showing Answer 0.

agar aap ko formula ki explaination chaheye tu see below thread

http://www.itdunya.com/t323335/

aur agar custom formatting ki tu try this

1. Apni range ko select karain.(remember sirf number hon text na ho)
2. format--->cell ya home tab sa bhee select kar sakte hain
3. formatting ki open hone wale window sa custom select karain.
4. aur apne marze ki formatting like 0.00, #,##.00 etc jo chahte hon
5.jo formatting aap n select ki type k row m show ho gi, is k last m aap sirf "/Km" ya apni marze ka text lekh dain. lekan double quote m hona chaheya aur ok kar dain.

aap ki sheet m jo range ap n select ki ho gi wahan par /KM lekha aae ga.

is par aap simple sum/average etc k formula laga sakte hain
regards,

[Asham Khan]

sumproduct(left(d5:d10,mmult(len(d5:d10)-len(substitute(d5:d10,{0,1,2,3,4,5,6,7,8,9},"")),{ 1;1;1;1;1;1;1;1;1;1}))+0)

Dear Kya Is Formulay Mai Aik Sy Ziyada Formulas Use Hovy Hain ??
Jesy K
SUMPRODUCT, MMULT, LEN, SUBSTITUTE
Agar Han Tu Kya In Ko Alag Alag Bhi Use Kiya Ja Skta hai ???