question for Excel Guru

Posts 30 · Viewed 9152 · Go to Last Post

· Snapshot

Like · Likedby · 0

Here is the case - StartDate EndDate Total amount January February March ...... 01/27/2007 06/30/2007 $1,875.00 01/01/2007 08/07/2007 $2,195.00 02/10/2007 05/01/2007 $1,500.00 How to calculate amounts for each month ?

Load Previous

nepalean

· Snapshot

Like · Liked by · 0

The word January should be above 732.22. The format was distorted here. But I wonder why excel screen shot doesnt appear in Sajha?

nepalean

· Snapshot

Like · Liked by · 0

this might work.

nepalean

· Snapshot

Like · Liked by · 0

Once more

raascal

· Snapshot

Like · Liked by · 0

Every day worths same amount. Lets not look at it as working days ... but as contract. What I need is a formula in one cell ( e.g. in January column) that holds true for all others cells in all other months.

nepalean

· Snapshot

Like · Liked by · 0

You can vary no. of days or you can put same no. of day for each month. Check your sajha email. it has screen shot. It may help you.

purush

· Snapshot

Like · Liked by · 0

Nepolean: Can you send it to me too? I am also interested to know the calculation buddy. Puruz

nepalean

· Snapshot

Like · Liked by · 0

ok buddy, check your email. But I have shown only one example and it follows the condition rascal mentioned. If there is different condition, you can put additional logical conditions. For description, follow above writings. Let me paste for you so that you can give a glance at once => Considering 7 working days and each day worths equal amount, here is one example. For the date cell, click format and click date. For amount , click format and click number. Current Sheet January 1/12/2007 3/25/2007 3500.00 732.22 1/25/2007 3/20/2007 2500.00 769.11 1/5/2007 2/20/2007 2200.00 744.09 Sheet 2 January February March 1/31/2007 02/31/07 3/31/2007 732.22 is E5 ; 1/31/05 is D5 in sheet 2 and I used this formula =((B5-C5)/D5)*(Sheet1!D5-B5) With the reference E5 for current sheet and D5 for sheet 2, all the there is no blank cell between consecutive rows and columns.

bond_oo7

· Snapshot

Like · Liked by · 0

hey whats ur email address? I will send my solution to you.

bond_oo7

· Snapshot

Like · Liked by · 0

Let me try this one more time..

purush

· Snapshot

Like · Liked by · 0

Hey Bond 007 and Nepalean I got the calculations and formulas that you guys are showing but when I try, it gives me totally different result. 2/12/2007 7/17/2007 4475.00 1/6/1900 (for deducting dates) 3/27/2007 8/14/2007 4700.00 1/5/1900 1/15/2007 7/12/2007 9225.00 1/6/1900 I don't know what is the problem. In nepalean's case, I used exactly the same formula all I get is #########. Puruz

nepalean

· Snapshot

Like · Liked by · 0

ye mula purush, tyo lekhekko pada na yar. ######### ayo bhane tyo cell ma right click garera format ma number select gara ( fomat ma date, scentific, etc huncha). Maile mathi bhanya k ta ? " For the date cell, click format and click date. For amount , click format and click number. "

nepalean

· Snapshot

Like · Liked by · 0

But mine method is different than Bond. My method includes exact no. of days for all the starting date to ending date ( whether its 1 or 2 or 25)

bond_oo7

· Snapshot

Like · Liked by · 0

if you see 1/1/1900.. all u need to do is 1. Rt Click 2. Format cells 3. select General (it will be date by default what why it showed that 1/1/1900) hope this helps!

raby

· Snapshot

Like · Liked by · 0

raascal

· Snapshot

Like · Liked by · 0

I tried ... but got values in decimals ... something really fishy going on!

NayaSadak

· Snapshot

Like · Liked by · 0

1875.00 2,195.00 1500.00 Monthly JAN 60.48 31.00 10.02 310.71 371.19 FEB 338.71 28.00 10.02 280.64 19.00 18.52 351.85 971.20 MAR 375.00 31.00 10.02 310.71 31.00 18.52 574.07 1259.78 APR 362.90 30.00 10.02 300.68 30.00 18.52 555.56 1219.14 MAY 375.00 31.00 10.02 310.71 1.00 18.52 18.52 704.23 JUN 362.90 30.00 10.02 300.68 0.00 663.59 JUL 31.00 10.02 310.71 0.00 310.71 AUG 7.00 10.02 70.16 0.00 70.16 219.00 81.00 5570.00

NayaSadak

· Snapshot

Like · Liked by · 0

Who ever you have asked this simple question must be a smarty A .....H..... because you gave your homework to us. You should have uesed a calculator and a calender and got the answer. Any way here is my math for you. Months 1875.00 2,195.00 1500.00 5570.00 JAN 60.48 310.71 371.19 FEB 338.71 280.64 351.85 971.20 MAR 375.00 310.71 574.07 1259.78 APR 362.90 300.68 555.56 1219.14 MAY 375.00 310.71 18.52 704.23 JUN 362.90 300.68 - 663.59 JUL -00 310.71 00- 310.71 AUG -00 70.16 00- 70.16 - - - 5570.00

NayaSadak

· Snapshot

Like · Liked by · 0

I think it is clearer now. 1875.00 2,195.00 1500.00 5570.00 JAN 60.48 310.71 00.00 371.19 FEB 338.71 280.64 351.85 971.20 MAR 375.00 310.71 574.07 1259.78 APR 362.90 300.68 555.56 1219.14 MAY 375.00 310.71 18.52 704.23 JUN 362.90 300.68 - 00.00 663.59 JUL - 00 310.71 00- 310.71 AUG -00 70.16 00- 70.16 Total 5,570.00

raascal

· Snapshot

Like · Liked by · 0

haha .... if I try to do this by using a fking calculator and a fking calendar, it will take me fking years coz the data I have posted above is just a preface of a mahabharat. thank you! I love you all.

bond_oo7

· Snapshot

Like · Liked by · 0

does our solution work? Is this what u were looking for?

Please log in to reply to this post

You can also log in using your Facebook