Formula request

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
Let me put the scenerio across
The value of A1 is 1
The value of B1 is 2
The value of C1 is 3
The value of D1 is 4
The value of E1 is 5

The value of A2 is 100
The value of B2 is 200
The value of C2 is 300
The value of D2 is 400
The value of E2 is 500

The value of G1 is Row1
The value of G2 is Row2

H1 and H2 accepts value from the user.

If i have to put the value for
H1 to 2 and H2 to 4, the H3 cell should sum up the corresponding value from 2 to 4(B1:D1) that is 200+300+400=800
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
That was a terrific reply within a short span of time. One more question, if we go to sum it up reverse (for example the value of G1 is more than the value of G2) an error box should appear instead of #REF!.

Thanx
Maxi
 
Upvote 0
Aladin Akyurek, don't wanna disappoint u but the one provided by the master looks good. Still I appreciate your efforts.
 
Upvote 0
On 2002-10-16 12:27, mac_see wrote:
That was a terrific reply within a short span of time. One more question, if we go to sum it up reverse (for example the value of G1 is more than the value of G2) an error box should appear instead of #REF!.

Thanx
Maxi

The INDEX version would still compute a sum, as it were, in reverse order. An "error box" would require VBA...
 
Upvote 0
#REF! appears if I put 5 in G1 and 4 in G2. Another strange thing is, If I keep a blank value in any of the cells (G1 or G2), it should bring up a error box (By error box, I mean, the text that we type in the Validation option under Data menu)
 
Upvote 0
>#REF! appears if I put 5 in G1 and 4 in G2.

Of course, semantically G1 should be the 1st column -- not the 2nd. You're original post didn't indicate otherwise.

>Another strange thing is, If I keep a blank
>value in any of the cells (G1 or G2), it
>should bring up a error box (By error box,
>I mean, the text that we type in the
>Validation option under Data menu)

Change your Validation so it doesn't permit blanks by unchecking the "Ignore blank" option.
 
Upvote 0
On 2002-10-16 12:47, mac_see wrote:
#REF! appears if I put 5 in G1 and 4 in G2. Another strange thing is, If I keep a blank value in any of the cells (G1 or G2), it should bring up a error box (By error box, I mean, the text that we type in the Validation option under Data menu)

Although data validation can be defeated, you could try...

For H1...

Set Allow to: Whole Number

Choose Between

Set Minimum to: 1

Set Maximum to: =COLUMN(E1)

For H2...

Set Allow to: Custom

Set Formula to: =(H2>=H1)*(H2-INT(H2)=0)*(H2>0)*(H2<=COLUMN(E1))

I already suggested what to use in H3... :biggrin:
This message was edited by aladin akyurek on 2002-10-17 02:20
 
Upvote 0
If you want to enter values in G1:G2 in any order consider using...
Book1
ABCDEFGHIJKL
1123455
21002003004005004
3900
4
5
6
Sheet5
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top